Thanks for this video. However, because I have cells like this 1.60943E+12 when I use the formula I end up with ########. Is that because that is a fx? Not sure how to fix this.
How do you automatically handle the switch between summer and winter time? Seems like the results would be an hour off for EST if you are looking at your results in Winter time?
Hi, this is a great video but I only received ###### (number signs) instead of the mm/dd/yyyy time P.M. display. In addition, my epoch time has thirteen digits and not ten digits.
@@nkumar2273 Yes I did but from another source. I used the following website: stackoverflow.com/questions/15164237/how-do-i-convert-a-unix-epoch-timestamp-into-a-human-readable-date-time-in-excel. Additionally, from the website I used the following formula in excel: "=A1/86400/1000+DATEVALUE("1-1-1970")+t/24" A1 represents the excel cell I selected. After using this formula in excel I used the following website to verify the result or calculation: www.epochconverter.com/. I hope this helps 🙂.
hi Michael, I've searched all over the internet because I had the same problem. You might already have found the answer, but it might be helpful in case anybody else has the same problem. Try to use milliseconds instead of seconds. For me this worked: =(TIMESTAMP + or - TIMEZONE IN MS/MS IN A DAY + EPOCH 1970) =(F3+7200000)/86400000+DATEVALUE("1/1/1970")
Seems like I have been too enthusiastic with the brackets try this =(F3+7200000)/86400000+DATEVALUE("1/1/1970") and make sure that you're cell is wide enough and that it's formatted correctly. Otherwise try to type it out instead of copy paste
So many other sites forget to tell you to format the field :P
You're a life saver.
Thanks for this video. However, because I have cells like this 1.60943E+12 when I use the formula I end up with ########. Is that because that is a fx? Not sure how to fix this.
Thanks bro. Still useful in 2021.👍🏼
So great!! I liked the breakdown of the formulas too, so that we can use that to adjust for day light savings time.
Thanks for the detailed video, Especially on calculating the timezone. I am done with report successfully
Very helpful! Thank you for posting this!
This is good thank you!
How do you automatically handle the switch between summer and winter time? Seems like the results would be an hour off for EST if you are looking at your results in Winter time?
This helped me a great deal; thank you! j
Thank U. It helped me Sir
Thank you...this is really helpful
thank you!! :)
Hi, this is a great video but I only received ###### (number signs) instead of the mm/dd/yyyy time P.M. display.
In addition, my epoch time has thirteen digits and not ten digits.
did you get the solution ? even am in need of it
@@nkumar2273 Yes I did but from another source.
I used the following website: stackoverflow.com/questions/15164237/how-do-i-convert-a-unix-epoch-timestamp-into-a-human-readable-date-time-in-excel.
Additionally, from the website I used the following formula in excel:
"=A1/86400/1000+DATEVALUE("1-1-1970")+t/24"
A1 represents the excel cell I selected.
After using this formula in excel I used the following website to verify the result or calculation:
www.epochconverter.com/.
I hope this helps 🙂.
Thank you!
Great. Thanks!
Thankyouuu
How can we show the milliseconds of the time?
No time to thank you!
thanks. :)
Hi am from India. So what should i add instead of 7200000 ? Can someone help me out.
Add 19800 seconds because IST is 5.5 hours ahead of GMT. So use the same formula as EEST sample but modify the seconds to 19800.
IN MY EXCEL DATE WITH TIME IS NOT COMING
You create a new column, but this time convert the unix code to 'time'. So you end up with a column for date and a column for time.
All i get is ###############################
hi Michael, I've searched all over the internet because I had the same problem. You might already have found the answer, but it might be helpful in case anybody else has the same problem. Try to use milliseconds instead of seconds.
For me this worked:
=(TIMESTAMP + or - TIMEZONE IN MS/MS IN A DAY + EPOCH 1970)
=(F3+7200000)/86400000+DATEVALUE("1/1/1970")
Hi Justin, I copy and pasted your formula, but it pops up as an error. Am I using this wrong?
Seems like I have been too enthusiastic with the brackets try this =(F3+7200000)/86400000+DATEVALUE("1/1/1970") and make sure that you're cell is wide enough and that it's formatted correctly. Otherwise try to type it out instead of copy paste
This comment was a lifesaver for me.
Thank you!!!