Hi All,
I am trying to convert a list of EPOCH dates/times to readable local time (EST). Currently I am using the formula =((A1-14400)/86400)+25569 and it works until it hits the DST time.
Is there a formula that would account for the local time (EST) as well as account for DST without having to manually change the formula to match the DST.
Attached is a sample worksheet.
Conversion Data.xlsx
Thanks
Assuming we are in the same year, you need to know the start and end dates of DST
3/8/15 2:00 AM in E2
11/1/15 2:00 AM in E3
=IF(AND(B2>E2, B2<E3), B2-"1:00", B2)
Does that work for you?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Hi ChemistB,
Thanks for the guidance.
I modified the formula you provided to **=IF(AND(A1>1425798000,A2<1446361200),(A1-14400)/86400+25569,(A1-18000)/86400+25569)**
Replaced the Start and End dates of DST in E2 and E3 to the actual EPOCH times.
This works for me, but wanted to check with you first if you would also approach it the same way?
Yes, that's probably how I would do it.
Thanks again for the help!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks