How do I Convert TEXT to EST Time ?
On Mon, 16 Nov 2009 21:34:31 -0800, fruitchunk
wrote:
Thanks a lot for your help it's working fine.
If you have some time can you explain me this folmula and how it is working?
It will helps us all out.
=1+TEXT(A1,"00\:00")-(MID(B1,4,5)+5)/24
Simply:
1. Convert Time in A1 from "military style" to a format Excel
understands as time.
2. Convert the GMT difference in B1 to a number and compute the
difference from EST.
a. Strip off the GMT portion by starting at the 4th character.
b. Add 5 for EST conversion.
c. Divide by 24 since Excel stores hours as fractions of a
day.
3. Add "1" since Excel does not like negative times.
--ron
|