date

Occasional Contributor

Hi

 

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?

Regards

Sara

8 Replies

@sabbesar 

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

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

 

=IF(MOD(A2,1)>=TIME(20,0,1),INT(A2)+1+TIME(4,45,0),A2)

 

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?
Sara

@sabbesar 

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 

sabbesar_0-1615477347854.png

 

@sabbesar 

I see that the values are in column L.

In the first available column, enter the formula

 

=IF(MOD(L2,1)>=TIME(20,0,1),INT(L2)+1+TIME(4,45,0),L2)

 

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)
Hans

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

Sara

@sabbesar 

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.