Occasional Contributor



I have following question:


I have an xls with all dates related to samples brought in the lab. Dates are expressed as such dd/mm/yyy hh:mm

If hh:mm are later than 20:00, I would like to convert these data into next day 04:45

Example 02/10/2020 23:30 has to change into 03/10/2020 04:45. Or 23/02/2021 21:01 will be 24/02/2021 04:45


How can I do this?



8 Replies


Let's say the dates are in A2 and down.

In another cell in row 2, for example in B1, enter the formula




Format the result as dd/mm/yyyy hh:mm, then fill down.

Hans, it seems not to work. In which format should cell 'A2' be?


I assumed that the values were date/time values formatted as dd/mm/yyyy hh:mm

Are they actually text values that look like dates?

@Hans Vogelaar 

This is how it is formatted 




I see that the values are in column L.

In the first available column, enter the formula




Format the same as column L, then fill down.

It seems that the error has something to do with the 'mod' function? =if(mod(L1356,1)>=TIME(20,0,1);INT(L1356)+1+TIME(45,5,0);L1356)

Thanks! It seems I had to use ; instead of ,! It is working!!



Ah - that is because you are using comma as decimal separator, so you have to use semi-colon to separate the arguments of a function.