function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Book_GuyBook_Guy 

Converting GMT To EST Time

Hi All,

 

I pulled the formula below off  another thread.  It works for times before 7:00PM and gives inaccurate sometimes negative results e.g. -1:48AM afterwards.

 

Any thoughts on how to adjust?

 

--David

 

IF(DATEVALUE(Now()) <= DATE(2012,03,11) || DATEVALUE(Now()) >= DATE(2012,11,04),

/* checks to see if date falls between Daylight Savings adjustment dates (would have to be updated once a year) */


TEXT(IF(VALUE(MID(TEXT(Now()),12,2)) <= 17, VALUE(MID(TEXT(Now()),12,2)) - 5, VALUE(MID(TEXT(Now()),12,2)) - 17)),

TEXT(IF(VALUE(MID(TEXT(Now()),12,2)) <= 16, VALUE(MID(TEXT(Now()),12,2)) - 4, VALUE(MID(TEXT(Now()),12,2)) - 16)))


/* adjusts hours for EST & Daylight Savings according to date */


&

MID(TEXT(Now()),14,3)


/* displays minutes */


&

IF(VALUE(MID(TEXT(Now()),12,2)) <= 16, " AM", " PM")


/* checks GMT time and adds 'AM' or 'PM' based on results */