CODEWITHSUNDEEP
excel-formula
JD_99
JD_99

Reputation: 43

Convert Excel time and date to Unix Epoch time

I have two cells - one with a date and one with a time.

The date cell A1 is formatted as Cell Type Date MM/DD/YY. The time cell B1 is formatted as Cell Type Time HH:00 AM/PM.

I am able to combine the cells using the formula TEXT(A1,"m/dd/yy ")&TEXT(B1,"h:mm AM/PM") with the result in cell C1.

For another program, I need to convert the date and time to Unix Epoch format and subtract the combined cell from 1/1/1970 (cell D1).

However, the formula (C1-D1)*86400 is not working. The formula gives a #VALUE error.

I understand the combined date and time has been converted to text so I set the cell format to the appropriate date and time format but that did not work either.

What's the appropriate procedure for performing this action?

Upvotes: 1

Views: 3361

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

Since dates are represented as integers and times are values between 0 and 1, you can combine column A and column B with simple addition and then format the cell appropriately.

DateTime

Then do as you suggested and take the difference from that date to 1/1/1970 and multiply by 86,400 seconds in a day to get the Unix Epoch time.

Unix Epch

Note: I accidentally put 886400 instead of 86400 in my screenshot. Please use the correct value.

Upvotes: 0

Related Questions