if Date and Time > 15:00 Then move to 07:00 the following day

[Andrew Skibbz]

Hi All,

I would like help with a formula that would move a date and time stamp to the following day at 07:00 if the date and timestamp was past 15:00.

So for instance if the date and time was 25/09/21 15:02 the new timestamp would be 26/09/21 07:00

If the timestamp is less than 15:00 then it just stays the same date.

Thanks in advance,

Best,

Skibbz

 

[GWteB]

Assuming your date & time are in cell A1, try this formula

=IF(A1<(INT(A1)+TIMEVALUE("15:00:00")),INT(A1)+TIMEVALUE("15:00:00"),INT(A1)+1+TIMEVALUE("07:00:00"))

 

[SQUIDD]

hi

testing your formula GWteB

would it not be

=IF(A1<(INT(A1)+TIMEVALUE("15:00:00")),A1,INT(A1)+1+TIMEVALUE("07:00:00"))

As everything was to stay the same if time was before 15:00

 

[Dave Patton]

Date and Time.xlsm
	C	D
2	25-Sep-21 15:02	26-Sep-21 07:00
3	25-Sep-21 06:00	25-Sep-21 06:00
4
22e

Cell Formulas
Range	Formula
D2:D3	D2	=IF(MOD(C2,1)>0.625,INT(C2)+1.291667,C2)