UTC Date is off

[tmoulder]

This is not the first time I've seen this, but I think it's the first time I ever brought it up:

I am using UTC to a data table to date/time-stamp an hourly production output.  On my first sample, the UTC returned: 3724498800

I used this formula to convert it in Excel:  =(F24/86400)+DATE(1900,1,1)  where F24 contains the UTC value.

This produced a result, after formatting, of 1/8/18 3:00 PM

Which would be great - except today is 1/9/18

Simple enough to correct for with DATE(1900,1,2), but the inaccuracy is concerning.  Am I doing something incorrectly?

Edit:  PS, I checked the Real Time Clock from Info Mode and it is correct.

Thanks!

 

TM

[Isakovic]

I tried reading UTC from PLC and got the same one day lag with that formula, but PLC has correct date data. Must be leap year missing a day every 400 years or something like that, there must be a code for conversion somewhere.

Why don't you put date into data table as integer or string? (I regretted it every time when I put it as a string)

[Ausman]

Edited:  incorrect info removed....me be tired and not thinkum clearly!

However, I use the RTC direct into my logging tables after some simple manipulation.  See below.  Might need another column (s?) in your table.  I do this method so no conversions are necessary to easily read the stored table.

cheers,

Aus

[GetAGrip]

I wrote a VBA function in Excel to do the conversion, but generally just us the following formula in Excel. Date = {UTC}/24/60/60+2. I make the date to be off 2 whole days from Excel's calculation in Windows (different if you are using the base 1/1/1904 Macintosh version of Excel). One of those days is because 1900 is not a leap year, but Excel erroneously thinks 2/29/1900 is a real day (which it is not). Unitronics get the math correct on this front. I think the second day comes from the fact that Excel seems to start the dates at 1/0/1900 not the first of January (one base math not zero base math).

[Ausman]

Interesting this came up again.  A little while ago I had to do something away from my normal methods of conversion, and thus had some converting to do.  The formula that works for me that converts the raw UTC correctly is:

=(A1/86400)+DATE(1900,1,1) with the result then formatted as dd/mm/yyyy hh:mm:ss   where A1 is the raw UTC.  

cheers, Aus

ps.  Please also note the Aussie date formatting.  The sensible one......not the silly USA and others style!!  🙂  Damn yankees...change everything 'cause they can....steering wheel side, cutlery use, blah blah.  I mean, who would want to describe a date as the month first?  Makes no sense at all from a logical perspective if you look at either way from a "drill down" view.  dd/mm/yy or yy/mm/dd.  🙄