date

%3CLINGO-SUB%20id%3D%22lingo-sub-2202539%22%20slang%3D%22en-US%22%3Edate%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2202539%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20following%20question%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20an%20xls%20with%20all%20dates%20related%20to%20samples%20brought%20in%20the%20lab.%20Dates%20are%20expressed%20as%20such%20dd%2Fmm%2Fyyy%20hh%3Amm%3C%2FP%3E%3CP%3EIf%20hh%3Amm%20are%20later%20than%2020%3A00%2C%20I%20would%20like%20to%20convert%20these%20data%20into%20next%20day%2004%3A45%3C%2FP%3E%3CP%3EExample%26nbsp%3B02%2F10%2F2020%2023%3A30%20has%20to%20change%20into%2003%2F10%2F2020%2004%3A45.%20Or%2023%2F02%2F2021%2021%3A01%20will%20be%2024%2F02%2F2021%2004%3A45%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20do%20this%3F%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3CP%3ESara%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2202539%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
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.