Excel date to Unix timestamp

I had an old Excel database with "human-readable" dates, like 2010.03.28 20:12:30 Theese 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 :)

To make up for the daylight saving time (starting on March's last sunday until October's last sunday) I had to use the following formula:

=IF(
  AND(
    A2>=EOMONTH(DATE(YEAR(A2);3;1);0)-MOD(WEEKDAY(EOMONTH(DATE(YEAR(A2);3;1);0);11);7);
    A2<=EOMONTH(DATE(YEAR(A2);10;1);0)-MOD(WEEKDAY(EOMONTH(DATE(YEAR(A2);10;1);0);11);7)
  );
  (A2-DATE(1970;1;1)-TIME(1;0;0))*24*60*60*1000;
  (A2-DATE(1970;1;1))*24*60*60*1000
)

Quick explanation:

If the date ["A2"] is between March's last sunday and October's last sunday [third and fourth code lines], then I'll be subtracting one hour [-TIME(1;0;0)] to the date.

If you can create a custom function (User Defined Function : UDF) : create a VBA module and copy these codes :

Function unix_time(Optional my_year, Optional my_month, Optional my_day, Optional my_hour, Optional my_minute, Optional my_second)
    Dim now_date, now_time, ref_date, ref_time

    my_year = IIf(IsMissing(my_year) = False, my_year, year(Now))
    my_month = IIf(IsMissing(my_month) = False, my_month, month(Now))
    my_day = IIf(IsMissing(my_day) = False, my_day, day(Now))

    my_hour = IIf(IsMissing(my_hour) = False, my_hour, hour(Now))
    my_minute = IIf(IsMissing(my_minute) = False, my_minute, minute(Now))
    my_second = IIf(IsMissing(my_second) = False, my_second, second(Now))

    now_date = DateSerial(my_year, my_month, my_day)
    now_time = TimeSerial(my_hour, my_minute, my_second)

    ref_date = DateSerial(1970, 1, 1)
    ref_time = TimeSerial(0, 0, 0)'You can change this line based on your time zone. for example : Tehran -> TimeSerial(3,30,0)

    now_date = now_date + now_time

    ref_date = ref_date + ref_time

    unix_time = (now_date - ref_date) * 86400
End Function

Then type in any cell you want: =unix_time()

Above code returns current date and time in unix format. If you need custom date and time, pass 6 parameters to "unix_time()" function.

Example : =unix_time(year, month, day, hour, minute, second)