[SOLVED] Equations using time

**JukeBoxHero** · 08-22-2012, 11:27 AM

I have a problem where i have some set times. These times are described in local hours. i need to find the GMT equivalent, and the EST equivalent.

When i do a simple =F2+C2 it works and gives me the correct value.

1) When i subtract more then there is value, it ends up with "#######" ex. =H2-C2
2) I need the 24 hour clock to work, where it uses 24:00 as 12PM instead of 00:00 ex. =i3-E3
3) i will also need to take the GMT time and subtract 5 hours for EST. Apparently subtracting a # from time does not work. Most likely because its a difference in formatting, so i temporarily used a column with the time value i wanted to subtract.

Are any of these possible?

I have attached a sample Sheet so that you can see what i am working with, hope it helps.

**jason.b75** · 08-22-2012, 12:51 PM

edited:-

Ignore what I asked, that was me being a bit dense, I realised after you want time, not time difference.

In H3 =D3+1-$C3

Fill right, then down.

**JukeBoxHero** · 08-22-2012, 01:41 PM

That worked. Thank you very much. If you don't mind me asking, what is the logic behind that?

Any ideas on how to fix the #2 or #3?

**mikerickson** · 08-22-2012, 01:49 PM

Subtracting from time works, as long as the difference is greater or equal to 0.

Try something like =MOD(A1-B1, 1)

**jason.b75** · 08-22-2012, 01:59 PM

The logic is to add a day to the time you're subtracting from in order to make that time greater than 24 hours, the value you subtract is time only so will always be less, eliminating the negative results that cause the ###### error.

To get the format you want in #2 takes a little more creative thinking.

In H3 =MOD(D3+1-$C3-"00:00:01",1)+"00:00:01"

Custom format [h]:mm

For question #3,

In L3 =MOD(H3+1-"04:59:59",1)+"00:00:01"

Custom format [h]:mm