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 Quote 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) Quote Link to comment Share on other sites More sharing options...
MVP 2022 Ausman Posted January 10, 2018 MVP 2022 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 Quote 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). Quote Link to comment Share on other sites More sharing options...
MVP 2022 Ausman Posted October 21, 2020 MVP 2022 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. 🙄 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.