time calculation

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pda52 - 13 Apr 2004 04:38 GMT

I need to do a check on the time.. if it is after midnight but before 5am
then I need to minus 1 day off of the date. How could I do this?

Reply to this Message

Dave Hawley - 13 Apr 2004 04:49 GMT

Hi

If the Date and Time is in cell A1, use

=IF(AND(MOD(A1,1)>VALUE("12:00 AM"),MOD(A1,1)<VALUE("05:00
AM")),A1+1,A1)

Reply to this Message

Dave Hawley - 13 Apr 2004 04:53 GMT

..shortened versions would be

=A1+AND(MOD(A1,1)>VALUE("12:00 AM"),MOD(A1,1)<VALUE("05:00 AM"))

Reply to this Message

Frank Kabel - 13 Apr 2004 06:56 GMT

Hi
if A1 stores a date/time value try
=A1-((MOD(A1,1)>=0)*(MOD(A1,1)<=5/24))

Signature

--
Regards
Frank Kabel
Frankfurt, Germany

> I need to do a check on the time.. if it is after midnight but before
> 5am then I need to minus 1 day off of the date. How could I do this?

Reply to this Message

pda52 - 13 Apr 2004 21:09 GMT

Thank you that works perfectly

> Hi
> if A1 stores a date/time value try
[quoted text clipped - 7 lines]
> > I need to do a check on the time.. if it is after midnight but before
> > 5am then I need to minus 1 day off of the date. How could I do this?

Reply to this Message