How do you filter between times of day regardless of the day

Follow

Saurabh Agashe

• April 16, 2015 21:21

How can I create a filter on date column to show entries that fall between 8:30AM and 5:30PM? The following formula is close, but not exact:

HOUR(#CallLogs.Call_Date) >= 8 && HOUR(#CallLogs.Call_Date) <= 16

Thanks.

0

• Official comment

  

  Saurabh Agashe

  • April 16, 2015 21:22

  You can also try the following filter as well:

  (ASDATE(FORMATDATE(#CallLogs.Call_Date;"HHmm");"HHmm") >= ASDATE("08:30";"HH:mm") && ASDATE(FORMATDATE(#CallLogs.Call_Date;"HHmm");"HHmm") <= ASDATE("17:30";"HH:mm"))
  

  Comment actions Permalink
• 

  Saurabh Agashe

  • April 16, 2015 21:21

  One way you could accomplish this would be to add the minutes to the already existing filter:

  HOUR(#CallLogs.Call_Date >= 8 && MINUTE(#CallLogs.Call_Date) >= 30 && HOUR() <= 16 && MINUTE(=#CallLogs.Call_Date) <= 30
  

  I received the following from another user confirming that this also works:

  ((HOUR(#CallLogs.Call_Date) > 8) && (HOUR(#CallLogs.Call_Date) < 17)) || ((HOUR(#CallLogs.Call_Date) == 8) && (MINUTE(#CallLogs.Call_Date) >= 30)) || ((HOUR(#CallLogs.Call_Date) == 17) && (MINUTE(#CallLogs.Call_Date) <= 30))
  

  0

  Comment actions Permalink

Please sign in to leave a comment.