Change Date and Time stamp when time is less than 8 AM

[jprlimey]

I have the following time and date stamp in a single cell

9/20/2019 7:17:55 AM

If the time portion is less than 8:00:00 AM, then i want an if statement that would take the existing date and time stamp and update the respective time portion to 8:00:00 AM

Result would be

9/20/2019 8:00:00 AM

Any help would be greatly appreciated

Thanks

[PaulM100]

Try: =IF(A2-INT(A2)<0.333333,INT(A2)+TIME(8,0,0),A2)

[ChemistB]

Please Login or Register  to view this content.

Does that work for you?

[jprlimey]

I tried both solution and both worked. It's not clear cut if one work better than the other.

To take one step further. As the 8am time could be different for some cases, i thought it best to put the 8am into a cell so the formula could reference it.

Attachment 686528

How best to reference the time from a cell into the formula

Thanks

[ChemistB]

Paul's and my solutions are basically the same, no advantage to using one over the other, depending on your preferences.
To your question, with the critical timepoint in H1
=IF(A2-INT(A2)<H1,INT(A2)+H1,A2)
or
=IF(MOD(A2,1)< H1, INT(A2)+H1, A2)

[Root_]

Another approach -- shorter formula with fewer reference calls:

=INT(A2)+MAX(MOD(A2,1),H1)

[jprlimey]

Chemist B and PaulM100,

Thanks so much for your assistance in solving this. Both solutions worked nice, i'll see over time which of the 2 i'll go with.

Thanks again