How to quickly convert milliseconds to a date in Excel?
Have you ever tried to calculate a date by a given number of milliseconds? For instance, 112233445566 is the number of milliseconds that far from a specific date, 1990, and now you want to convert the milliseconds to a date as below screenshot shown.
Convert UNIX timestamp to Excel date (in EST timezone) - gist:1040285.
Convert milliseconds to a date
Amazing! Using Efficient Tabs in Excel Like Chrome, Firefox and Safari!
Save 50% of your time, and reduce thousands of mouse clicks for you every day!
With one formula, you can quickly handle this job.
Select a cell which you will place the date in, type this formula = (G2/86400000)+DATE(1990,1,1), G2 is the milliseconds cell, 1990,1,1 is the start date, press Enter key and drag autofill handle down to the cells for getting a list of decimal number. See screenshot:
Go to Home tab and select Short Date from the Number Format drop-down list in Number group to format the numbers as dates. See screenshot:
Apply Date formatting (change date to multiple date formatting as you need) |
Relative Articles
Recommended Productivity Tools for Excel
Kutools for Excel Helps You Always Finish Work Ahead of Time, and Stand Out From Crowd
- More than300 powerful advanced features, designed for1500 work scenarios, increasing productivity by70%, give you more time to take care of family and enjoy life.
- No longer need memorizing formulas and VBA codes, give your brain a rest from now on.
- Become an Excel expert in 3 minutes, Complicated and repeated operations can be done in seconds,
- Reduce thousands of keyboard & mouse operations every day, say goodbye to occupational diseases now.
- 110,000 highly effective people and 300+ world-renowned companies' choice.
- 60-day full features free trial. 60-day money back guarantees. 2 years of free upgrade and support.
Brings Tabbed Browsing and Editing to Microsoft Office, Far More Powerful Than The Browser's Tabs
- Office Tab is designed for Word, Excel, PowerPoint and Other Office Applications: Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by50%, and reduces hundreds of mouse clicks for you every day!
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
Does anyone know how to convert an Excel date to a correct Unix timestamp?
HagbardHagbard
9 Answers
Non of these worked for me... when I converted the timestamp back it's 4 years off.
This worked perfectly:
=(A2-DATE(1970,1,1))*86400
Credit goes to: Filip Czaja http://fczaja.blogspot.ca
Original Post: http://fczaja.blogspot.ca/2011/06/convert-excel-date-into-timestamp.html
CybercampbellCybercampbell
Windows and Mac Excel (2011):
MAC OS X (2007):
For Reference:
Jeff WuJeff Wu
If we assume the date in Excel is in A1 cell formatted as Date and the Unix timestamp should be in a A2 cell formatted as number the formula in A2 should be:
![Excel Unix Timestamp To Date Excel Unix Timestamp To Date](/uploads/1/2/5/6/125623228/481169035.png)
= (A1 * 86400) - 2209075200
where:
86400 is the number of seconds in the day2209075200 is the number of seconds between 1900-01-01 and 1970-01-01 which are the base dates for Excel and Unix timestamps.
The above is true for Windows. On Mac the base date in Excel is 1904-01-01 and the seconds number should be corrected to: 2082844800
GrendlerGrendler
Here is a mapping for reference, assuming UTC for spreadsheet systems like Microsoft Excel:
* “Jan Zero, 1900” is 1899/12/31; see the Bug section below. † Excel 2011 for Mac (and older) use the 1904 date system.
As I often use
awk
to process CSV and space-delimited content, I developed a way to convert UNIX epoch to timezone/DST-appropriate Excel date format:I used
echo
for this example, but you can pipe a file where the first column (for the first cell in .csv format, call it as awk -F,
) is a UNIX epoch. Alter $1
to represent your desired column/cell number or use a variable instead.This makes a system call to
date
. If you will reliably have the GNU version, you can remove the 2>/dev/null || date … +%%z
and the second , $1
. Given how common GNU is, I wouldn't recommend assuming BSD's version.The
getline
reads the time zone offset outputted by date +%z
into tz
, which is then translated into hours
. The format will be like -0700
(PDT) or +0530
(IST), so the first substring extracted is 07
or 05
, the second is 00
or 30
(then divided by 60 to be expressed in hours), and the third use of tz
sees whether our offset is negative and alters hours
if needed.The formula given in all of the other answers on this page is used to set
excel
, with the addition of the daylight-savings-aware time zone adjustment as hours/24
.If you're on an older version of Excel for Mac, you'll need to use
24107
in place of 25569
(see the mapping above).![Excel convert unix timestamp to date Excel convert unix timestamp to date](https://cdn.extendoffice.com/images/stories/doc-excel/convert-date-unixtimestamp/doc-convert-date-unix-6.png)
To convert any arbitrary non-epoch time to Excel-friendly times with GNU date:
This is basically the same code, but the
date -d
no longer has an @
to represent unix epoch (given how capable the string parser is, I'm actually surprised the @
is mandatory; what other date format has 9-10 digits?) and it's now asked for two outputs: the epoch and the time zone offset. You could therefore use e.g. @1234567890
as an input.Bug
Lotus 1-2-3 (the original spreadsheet software) intentionally treated 1900 as a leap year despite the fact that it was not (this reduced the codebase at a time when every byte counted). Microsoft Excel retained this bug for compatibility, skipping day 60 (the fictitious 1900/02/29), retaining Lotus 1-2-3's mapping of day 59 to 1900/02/28. LibreOffice instead assigned day 60 to 1900/02/28 and pushed all previous days back one.
Any date before 1900/03/01 could be as much as a day off:
Excel doesn't acknowledge negative dates and has a special definition of the Zeroth of January (1899/12/31) for day zero. Internally, Excel does indeed handle negative dates (they're just numbers after all), but it displays them as numbers since it doesn't know how to display them as dates (nor can it convert older dates into negative numbers). Feb 29 1900, a day that never happened, is recognized by Excel but not LibreOffice.
Adam KatzAdam Katz
Because my edits to the above were rejected (did any of you actually try?), here's what you really need to make this work:
Windows (And Mac Office 2011+):
- Unix Timestamp =
(Excel Timestamp - 25569) * 86400
- Excel Timestamp =
(Unix Timestamp / 86400) + 25569
MAC OS X (pre Office 2011):
- Unix Timestamp =
(Excel Timestamp - 24107) * 86400
- Excel Timestamp =
(Unix Timestamp / 86400) + 24107
radicandradicand
You're apparently off by one day, exactly 86400 seconds.Use the number 2209161600Not the number 2209075200If you Google the two numbers, you'll find support for the above.I tried your formula but was always coming up 1 day different from my server. It's not obvious from the unix timestamp unless you think in unix instead of human time ;-) but if you double check then you'll see this might be correct.
MarkMark
I had an old Excel database with 'human-readable' dates, like 2010.03.28 20:12:30Theese dates were in UTC+1 (CET) and needed to convert it to epoch time.
I used the =(A4-DATE(1970;1;1))*86400-3600 formula to convert the dates to epoch time from the A column to B column values.Check your timezone offset and make a math with it. 1 hour is 3600 seconds.
The only thing why i write here an anwser, you can see that this topic is more than 5 years old is that i use the new Excel versions and also red posts in this topic, but they're incorrect. The DATE(1970;1;1). Here the 1970 and the January needs to be separated with ; and not with ,
If you're also experiencing this issue, hope it helps you.Have a nice day :)
Sorbán BélaSorbán Béla
None of the current answers worked for me because my data was in this format from the unix side:
2016-02-02 19:21:42 UTC
I needed to convert this to Epoch to allow referencing other data which had epoch timestamps.
- Create a new column for the date part and parse with this formula
- As other Grendler has stated here already, create another column
- Create another column with just the time added together to get total seconds:
- Create a last column that just adds the last two columns together:
DranyarDranyar
Here's my ultimate answer to this.
Also apparently javascript's
new Date(year, month, day)
constructor doesn't account for leap seconds too.asdfasdfadsasdfasdfads