Link to home
Start Free TrialLog in
Avatar of NVIT
NVITFlag for United States of America

asked on

Need VBS version of Excel date conversion

I have this in Excel but need a vbscript version to run on CMD line, i.e. cscript dateconvert.vbs 1255635089

A5=1255635089
B5=A5/86400+DATE(1970,1,1)-(10/24)

I have this so far...

BTW, I'm not an expert VBS coder as you can see. So, I'm open to better / cleaner solution.

EpochToDate.vbs
Set objArgs = WScript.Arguments
nEpochVal = objArgs(0)
Dim dblVbEpoch
    dblVbEpoch = CDbl(DateAdd("s", nEpochVal, #1970/1/1#))

sTime=FormatDateTime(DateAdd("n", CurrentTZOffset(), CDate(dblVbEpoch)),3)
sTime=Replace(sTime,":","_")
sTime=Replace(sTime," ","")
WScript.Echo myDateFormat(DateAdd("n", CurrentTZOffset(), CDate(dblVbEpoch))) & " " & sTime

Function CurrentTZOffset()
    With CreateObject("WScript.Shell") 
        CurrentTZOffset = - .RegRead( _ 
        "HKLM\System\CurrentControlSet\Control\TimeZoneInformation\ActiveTimeBias")
    End With
End Function

Function myDateFormat(myDate)
    d = WhatEver(Day(myDate))
    m = WhatEver(Month(myDate))    
    y = Year(myDate)
    myDateFormat= y & "_" & m & "_" & d
End Function

Function WhatEver(num)
    If(Len(num)=1) Then
        WhatEver="0"&num
    Else
        WhatEver=num
    End If
End Function

Open in new window


But, running cscript /nologo EpochToDate.vbs 1255635089
... echos it as: 2009_10_15 9_31_29AM
... which is fine except:

- I don't want the AM or PM suffix
- If the hour is a single digit, I'd like it prefixed with a zero. This, for sorting reasons.
Avatar of David Johnson, CD
David Johnson, CD
Flag of Canada image

Set fso = CreateObject ("Scripting.FileSystemObject")
Set objArgs = WScript.Arguments
nEpochVal = objArgs(0)
Dim dblVbEpoch
Dim Mydate
dblVbEpoch = CDbl(DateAdd("s", nEpochVal, #1970/1/1#))
Mydate = DateAdd("n", CurrentTZOffset(), CDate(dblVbEpoch))
Mydate = Replace(Mydate,"-","_")
Mydate = Replace(Mydate," ","_")
Mydate = Replace(Mydate,":","_")
WScript.echo(Mydate)

Function CurrentTZOffset()
    With CreateObject("WScript.Shell") 
        CurrentTZOffset = - .RegRead( _ 
        "HKLM\System\CurrentControlSet\Control\TimeZoneInformation\ActiveTimeBias")
    End With
End Function

Open in new window


08_Dec_2009_6_17_30_PM  (I am in EDT5EST and my regional settings are set to dd-MMM-YYYY
Avatar of NVIT

ASKER

David,

I haven't had a chance to try your solution. I assume your result 08_Dec_2009_6_17_30_PM is based on it.

Still...
- I don't want the AM or PM suffix.
- If the hour is a single digit, I'd like it prefixed with a zero.

To clarify, 2009_12_08_06_17_30_PM should show (localized) as 2009_12_08_18_17_30
ASKER CERTIFIED SOLUTION
Avatar of Bill Prew
Bill Prew

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of NVIT

ASKER

Thanks, Bill!