Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Custom column based on time value

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?

 

 

 

 

 

Need Help
  • radpir's avatar
    radpir
    9 years ago

    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

  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi Anonymous,


    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

5 Replies