Time Format within IF Statement

[#1]

I have a DateTime field in my database.  I need an expression to show whether the time is "Day" or "Night".  "Night" is defined as between 19:00 (7:00 PM) and 07:00 (7:00 AM).  I need some help with the time syntax.

I tried these, but nothing is working so far:

IF(TIME(DateTime)>1900 OR TIME(DateTime)<0700,"Night","Day")

IF(TIME(DateTime)>19 OR TIME(DateTime)<07,"Night","Day")

IF(TIME(DateTime)>19:00 OR TIME(DateTime)<07:00,"Night","Day")

What am I doing wrong here?  Is it a syntax problem with the times, or is it the IF statement?

Thanks,

Sam.

[Miguel_Angel_Baeyens]

Hi Sam,

It's rather a question of formatting. I'd use instead the InDayToTime function:

=If(InDayToTime(Time('23:00'), Time('19:00'), 0, Num(Time('07:00'))), 'Day', 'Night')

So the first parameter is the time, the second is the end of the day and the third is the beginning of the day. If InDayToTime returns -1 (true) then the time is between 07:00 and 19:00, so Day, otherwise Night.

Hope that helps.

Miguel

[#3]

Miguel:

Thanks for the idea.  I tried editing your expressions slightly, to read the time from my OrderDateTime field.  Unfortunately, that expression is returning "Night" for every record, so there's still something wrong with the syntax:

=If(InDayToTime(Time(OrderDateTime,'hh:mm'), Time('19:00'), 0, Num(Time('07:00'))), 'Day', 'Night')

Any idea what I'm doing wrong?

[Miguel_Angel_Baeyens]

Hi Sam,

I'm pretty sure it's something related to formats. You need to extract the time part from your timestamp, something like

Time(Frac(OrderDateTime))

Hope that helps.

Miguel