time calculation

[pda52]

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?

 

[Dave Hawley]

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)

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Dave Hawley]

...shortened versions would be

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

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Frank Kabel]

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

 

[pda52]

Thank you that works perfectly

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