viewpoint-particle

Author Topic: FromOADate()  (Read 171 times)

stanl

  • Pundit
  • *****
  • Posts: 1171
FromOADate()
« on: June 14, 2020, 03:34:44 am »
Ran into an issue with a script that removes unprintable characters from excel rows. Using clearformats() and dates become numeric offsets from 1/1/1900.  Discovered CLR and WB Timeformat() could address this.
Code: Winbatch

xlDate = 43214.6123
dt = ObjectClrNew("System.DateTime")
Message("Excel Date:":xlDate,TimeFormat(dt.FromOADate(xlDate),"MM/dd/yyyy hh:mm:ss t"))
Exit
 

stanl

  • Pundit
  • *****
  • Posts: 1171
Re: FromOADate()
« Reply #1 on: June 17, 2020, 03:28:18 am »
Here is a little more CLR Datetime functionality. WB is well furnished with Date/Time functions, so the script could be written without CLR:
Code: Winbatch

d = "2/16/2008 12:15:12 PM"
dt  = ObjectClrNew("System.DateTime")
dt  = dt.Parse(d)
TZ = ObjectClrNew("System.TimeZoneInfo")
TZID = TZ.Local
TUtc = TZ.ConvertTimeToUtc(dt)
Message("Time Zone: ":TZID.DisplayName,"String DateTime: ":d:@LF:"Converted: ":dt:@LF:"UTC: ":TUtc)


 

kdmoyers

  • Sr. Member
  • ****
  • Posts: 339
Re: FromOADate()
« Reply #2 on: June 17, 2020, 10:34:47 am »
This is handy, thanks!
-K
The mind is everything; What you think, you become.

stanl

  • Pundit
  • *****
  • Posts: 1171
Re: FromOADate()
« Reply #3 on: June 18, 2020, 02:41:02 am »
This is handy, thanks!
-K


Of course converting an historical timestamp to utc is flawed unless you calculate the bias based on whether the date falls within DST. For realtime conversion:
Code: Winbatch

dt1 = ObjectClrNew("System.DateTime")
dt2 = ObjectClrNew("System.DateTime")
dt1=dt1.Now  ;same as WB TimeYmdHm()
dt2=dt2.UtcNow
Message("Local: ":dt1,"UTC: ":dt2)