External Moderators tmoulder Posted January 9, 2018 External Moderators Report Share Posted January 9, 2018 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 Link to comment Share on other sites More sharing options...
Isakovic Posted January 10, 2018 Report Share Posted January 10, 2018 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) Link to comment Share on other sites More sharing options...
MVP 2023 Ausman Posted January 10, 2018 MVP 2023 Report Share Posted January 10, 2018 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 Link to comment Share on other sites More sharing options...
GetAGrip Posted October 21, 2020 Report Share Posted October 21, 2020 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). Link to comment Share on other sites More sharing options...
MVP 2023 Ausman Posted October 21, 2020 MVP 2023 Report Share Posted October 21, 2020 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. 🙄 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now