Solved Testing DateTime for TRUE or FALSE on Time Part

Microsoft Excel 2010
August 31, 2012 at 09:34:44
Specs: Windows XP
Help please

I am testing consumtion times against a DateTime column to determine what the tariff should be. All the formulae work fine until it must return TRUE or FALSE against the midnight lines. Does not matter which way I tried, it always returns FALSE.

The statement for column 5:

=IF(OR(J249="sat",J249="sun"),"",AND(MOD($A249,1)>TIME(21,0,0),MOD($A249,1)<TIME(23,59,0)))

The statement for column 6:

=IF(OR(J249="Sat",J249="Sun"),"",AND(MOD($A249,1)>TIME(0,0,0),MOD($A249,1)<TIME(6,59,0)))

This should have been one formula testing from 21:00 the previous day to 07:00 the next day, but because of the midnight problem I tried to split it up; without any success:

The data below; the results of the statements in columns 5 & 6:

1/23/2012 21:30 Mon FALSE FALSE TRUE FALSE
1/23/2012 22:00 Mon FALSE FALSE TRUE FALSE
1/23/2012 22:30 Mon FALSE FALSE TRUE FALSE
1/23/2012 23:00 Mon FALSE FALSE TRUE FALSE
1/23/2012 23:30 Mon FALSE FALSE TRUE FALSE
1/24/2012 0:00 Tue FALSE FALSE FALSE FALSE ????
1/24/2012 0:30 Tue FALSE FALSE FALSE TRUE
1/24/2012 1:00 Tue FALSE FALSE FALSE TRUE
1/24/2012 1:30 Tue FALSE FALSE FALSE TRUE

How do I get past this midnight nightmare?

Is there another way to approach it then?

Thanking you in anticipation.


See More: Testing DateTime for TRUE or FALSE on Time Part

Report •


#1
August 31, 2012 at 11:33:08
✔ Best Answer
MOD($A249,1)>TIME(0,0,0)
The only time this will ever be FALSE is if the time is midnight. If you wish to include midnight, remove this test entirely.

How To Ask Questions The Smart Way


Report •

#2
October 2, 2012 at 10:04:38
Thank you Razor2.3 for the feedback. By removing the test the problem was solved.

Thank you again.


Report •
Related Solutions


Ask Question