Help with Date Functions

[Guest]

I need help with the following functions for dates. In Column A I have a
start date (including time) and in Column B I have the end date (including
time). I have been tasked with finding out if the end time is past 8:00 a.m.
each day. If the end time is past 8:00 a.m. I must place a “Y†in Column C
or an “N†in Column C.
This out my data looks:

Column A Column B
Start Time End Time
10/20/2005 23:00 10/20/2005 23:53
10/20/2005 10:00 10/21/2005 8:30
How do I check the time with the date being in the beginning of this data?
The date maybe the same day or could be the next day.

Thanks!

 

[Guest]

Try the below formula which will check the time portion of the data/time that
is in column B.

=IF(MOD(B1,1)>0.33333,"Y","N")

There may be an easier way but this appears to work.

Thanks,
Bill Horton

 

[Guest]

In C2: =IF((B2-INT(B2))>=TIME(8,0,0),"Y","N"). This will put a Y in C2 if
the end TIME in B2 is 8am or later. Fill this formula down into each row of
your data.
--Bruce

 

[Guest]

Thanks that works. Now if they also want to check to make sure it is >= 8:00
a.m.and before 6:00 p.m. how would you write this. I am thinking using the
AND or the OR but I am not sure.

 

[Guest]

=IF(and((B2-INT(B2))>=TIME(8,0,0),(B2-INT(B2))<TIME(18,0,0)),"Y","N").