Error in calculating time difference

Question

Hello,

In one of my cells I have the time in UTC. In another cell, I use this time to calculate it in EST.

I noticed today that there's an issue with the calculation on a specific time: 2021-04-30 6:10:48 PM

When doing the calculation it comes up as 4-30-21 12:48 PM which is wrong.

The weird thing is that if I change the time 2021-04-30 6:10:48 PM to 6:10:47 PM or 6:10:49 PM, it calculates it correctly to 4-30-21 2:10 PM.

Formula I use is =LEFT(C118,LEN(C118)-3)+(VLOOKUP(A118,Variables!A:B,2,FALSE)/24)

     where A118 is a location code that gives the timezone to calculate on (-4 for example to get EST) and C118 is 2021-04-30 6:10:48 PM

Anyone have any idea why an exact time to the second breaks the calculation? Thank you.

Answer 1

Hi

Try the formula in cell C118=A118-TIME(4,0,0)

Make sure the UTC time value entered in A118 is NOT a TEXT value

It must be a real Excel time value as shown in the picture below

I hope this helps you

Regards

Jeovany

Answer 2

Hi Rudi,

May I know if you still need help? If so, you can post back and we will try our best to help you.

Best Regards,

Sukie

Answer 3

You are working with dates and not strings, so just use date math:

=C118+(VLOOKUP(A118,Variables!A:B,2,FALSE)/24)

It is possible that you have a mixture of strings and dates - if you apply a different time format, say, just time - the display on the sheet should change if they are actual dates and will not change if the values are strings that look like dates.

Your date time equates exactly to this number (unluckily for your formula):

44316.7575

Adding a minute to that time makes makes the value

44316.757511574

which works with your formula.