Custom column based on time value

[jreynolds]

Hi,

I've not worked with creating a custom formulae where time comparisions are used and was hoping for some pointers.

I have two columns:

1. AppointmentBookedTime: the time a user booked an appointment using our system. eg, 04/07/16 09:22:33
2. AppointmentTime: the time when the actual appointment is to take place (in the future) e.g. 05/08/ 11:30:00

I want to create a cusotm column called BookedTimeBusinessHours that returns a Y/N response for each row where:

• Y = if an appointment was booked between 9am and 5pm
• N = if an appointment was booked between 5pm and 9am

Any tips on the formulae requried to acheive this?

[radpir · Accepted solution]

hi there,

use this formula for the BookedTimeBusinessHours column:

= if Time.Hour([AppointmentBookedTime]) >= 9 and Time.Hour([AppointmentBookedTime]) <= 17 

   then "Y"

   else "N"

hope this works for you,

radpir

[MAAbdullah47]

Please Help Me to answer similar case on this new thread : http://community.powerbi.com/t5/Desktop/Creating-conditional-column-based-on-time-for-another-column...

[v-shex-msft · Accepted solution]

Hi @jreynolds,

radpir's solution seems well, I convert this to dax, you can refer to it:

BookedTimeBusinessHours =
var currentTime=TIME(HOUR([AppointmentBookedTime]),MINUTE([AppointmentBookedTime]),SECOND([AppointmentBookedTime]))
return
IF(AND(currentTime>=TIME(9,0,0),currentTime<=TIME(17,0,0)),"Y","N")

Regards,

Xiaoxin Sheng

[MAAbdullah47]

Please Help Me to answer similar case on this new thread: http://community.powerbi.com/t5/Desktop/Creating-conditional-column-based-on-time-for-another-column...

[jreynolds]

Thank, Xiaoxin! Worked perfectly.

[radpir · Accepted solution]

hi there,

use this formula for the BookedTimeBusinessHours column:

= if Time.Hour([AppointmentBookedTime]) >= 9 and Time.Hour([AppointmentBookedTime]) <= 17 

   then "Y"

   else "N"

hope this works for you,

radpir