Sign in to follow this  
Followers 0

String Value convert to date format

3 posts in this topic

i have a third party OPC server transmitting data to 1756-L73-A17 to controller derived tags that i have created in the PLC. all of these tags are working great and retriveving the raw data from the OPC. i have one tag Titled CNA_Last_Measurement_Date_Time, this raw data transmits in the form as the raw data of 43865.7090393519, but i need it to be displayed as 02/10/2020 07:05:39. i have researched all over to find a way to do this either in the PLC or on the HMI and have not found a way to convert the Datevalue numer to a date time format. 

this tag changes/updates based on the last CNA measurement that was taken.

does any one have a way to accomplish this via the PLC or The HMI?


Share this post

Link to post
Share on other sites

I'm assuming this is a real value and is a time format starting from 1900  excel uses this same thing put that number in excel reformat to time with date and you get your number 

so start doing a little math  43865.7090393519

43865 days from 1900

.7090393519 of the current day so 24*.7090393519=17.0169444456‬ so then 17 hours into the day then keep going with now the decimal left is what part of the hour is left 

when your done you should get 

2/4/20 5:01 PM

Dont forget about leap years this could get interesting 

1 person likes this

Share this post

Link to post
Share on other sites

well, you need to know what exact calendar is used.... Gregorian, Julian or something else.


day=value MOD 365.25 // account for leap year 
year = 1900 + (value-day)*365.25 // returns 2020


if day>31 then // exceeds January?
  month - month+1
  day = day = 31)


Share this post

Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  
Followers 0