FormatDateTime Function not producing desired results in vbscript

Hello,

I'm working on a VBScript -- thanks to BlueDevilFan's assistants several times before -- I'm trying to use the FormatDateTime Function to display the start date/time of a meeting --  in the following format:

11/18/2015 08:00 AM

Using the following line:  

excSht.Cells(lngRow, 2) = (FormatDateTime(olkAppt.Start,2) &" "& FormatDateTime(olkAppt.Start,3))

Open in new window


I should be able to just use the default FormatDateTime function without any numbers (0), correct?  

When I use the exact line above - just without the space, it displays the date/time in the correct format -- but without a space between the date and time.  

excSht.Cells(lngRow, 2) = (FormatDateTime(olkAppt.Start,2) & FormatDateTime(olkAppt.Start,3))

Open in new window


As soon as I put the space in, it puts double times in, and without the AM/PM...  

I've also tried the obvious:
excSht.Cells(lngRow, 2) = olkAppt.Start

Open in new window

-- but it doesn't provide the correct format -- ex 11/18/2015 08:00 AM

If it matters, this was used to create an Excel document - now being used to create a tab-delimited text file -- it doesn't seem to change any of the other functions...  

Thank you,

Chris
damoncf1234Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
In Excel, this small VBA procedure seems to behave as we would expect.  Can you try it?

Option Explicit

Sub Tester()
    Cells(1, 1) = (FormatDateTime(Now, 2) & FormatDateTime(Now, 3))
    Cells(2, 1) = (FormatDateTime(Now, 2) & " " & FormatDateTime(Now, 3))
    Cells(3, 1) = (FormatDateTime(Now))
End Sub

Open in new window

~bp
David LeeCommented:
Hi, Chris.

The FormatDateTime function is, in my opinion, nearly useless.  I replaced it a long time ago with a function that allows me complete control over the formatting.  That function and a support function is depends on are below.  Just pass the OutlookDateFormat function the date/time you want to format.

Function OutlookDateFormat(varDate)
    Dim intHour, strAMPM
    intHour = Hour(varDate)
    If intHour > 12 Then
        intHour = intHour - 12
        strAMPM = "PM"
    Else
        strAMPM = "AM"
    End If
    OutlookDateFormat = Month(varDate) & "/" & Day(varDate) & "/" & Year(varDate) & " " & StrZero(intHour, 2) & ":" & StrZero(Minute(varDate), 2) & " " & strAMPM
End Function

Private Function StrZero(varNumber As Variant, intLength As Integer) As String
    ' Purpose: Pad a number with zeroes to the given length and return it as a string.'
    ' Written: 4/24/2009'
    ' Author:  TechnicLee'
    ' Outlook: All versions'
    Dim intItemLength
    If IsNumeric(varNumber) Then
        intItemLength = Len(CStr(Int(varNumber)))
        If intItemLength < intLength Then
            StrZero = String(intLength - intItemLength, "0") & varNumber
        Else
            StrZero = varNumber
        End If
    Else
        StrZero = varNumber
    End If
End Function

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
damoncf1234Author Commented:
Bill,

Thanks for the quick response.  The top two lines look identical to what I was using today -- only difference would be that instead of using "now" I'm using the start times of meetings from an outlook public calendar (and end times on the next line)
I just left work for the evening and it's on a network not connected to the internet.  

It works as expected when I put the functions with 2 and 3 options next to each other - but as soon as I added the single space between the two, it switched to a 24-hour time.  

I can look at it again in the morning and get back to you.  

Chris
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

damoncf1234Author Commented:
BlueDevilFan,

Good to hear from you again - I actually have both of those functions at the bottom of my script.  It's basically one of the earlier ones you helped me with - just dumps all calendar entries into a single tab-delimited file.  

I tried using your function on the appt start time and end times - but it doesn't give me the correct format -- which seems strange because I looked at your function and it seems like it should format it perfectly.

I don't have access to the script from home - I'll post a copy of it on here in the morning - maybe there's something simple I'm missing.  

Thank you again!

Chris
damoncf1234Author Commented:
BlueDevilFan,

So simply put (olkAppt.Start) in one of the fields, it appears as 11/19/2015 16:00 (basically exactly as we want it -- but with a 24-hour time -- instead of 4:00 PM

If I put OutlookDateFormat(olkAppt.Start), I get the same results -- 11/19/2015 16:00
(which makes no sense, because I see the format of your OutlookDateFormat function -- which shows the date format, the time format, the AM/PM at the end -- but for some reason, it doesn't 'apply'.  

If I use datDate & FormatDateTime(olkAppt.Start,3), I get

11/19/20158:15:00 AM (no space between the date and time, and extra zeros for seconds

If I use datDate &" "& FormatDateTime(olkAppt.Start,3), I get
11/19/2015 16:00 (gives me the space between the date and time, but the time switches back to 24-hour time.  

I've used your OutlookDateFormat function and the associated function (included in this script) -- but for some reason it doesn't apply the 12-hour time and AM PM format to the time...  Thank you,

Chris
damoncf1234Author Commented:
BlueDevilFan,

Hello - I have your two functions in the script -- but for some reason, they're not producing the desired results.  

After messing around a bit, I started "taking off" sections of the function -- and was able to produce good results (correctly-formatted dates and times in the tab-delimited file) -- by modifying this line in the function:
OutlookDateFormat = Month(varDate) & "/" & Day(varDate) & "/" & Year(varDate) & " " & StrZero(intHour, 2) & ":" & StrZero(Minute(varDate), 2) & " " & strAMPM

Open in new window


I started from the end, and successfully got the hours and minutes and AM/PM to appear in the results; then added the year, forward slash, day, forward slash -- and everything was appearing/formatted correctly -- using this line:

OutlookDateFormat = "/" & Day(varDate) & "/" & Year(varDate) & " " & StrZero(intHour, 2) & ":" & StrZero(Minute(varDate), 2) & " " & strAMPM

Open in new window


But as soon as I added the
Month(varDate) & 

Open in new window

to the function, the results went back to their old format -- 24-hour hours, no AM/PM, etc.  

It's almost like it doesn't recognize "Month" for some reason...  

Up in the main script, I used
excSht.Cells(lngRow, 2) = OutlookDateFormat(olkAppt.Start) 

Open in new window

to use the function...  

I've also tried to use datDate &" "& FormatDateTime(olkAppt.Start,3), and I get
 11/19/2015 16:00 (gives me the space between the date and time, and with some combinations, it switches the time to the 12-hour time "twice".  

Here's another question -- how do you know which variables/options are available under "olkAppt"?  For example -- is there a list somewhere that shows standard options -- like olkAppt.Start, olkAppt.End, olkAppt.Subject, etc.?  

Right now I have the script running by just using olkAppt.Start and olkAppt.End (which displays a 24-hour time) - but we'd prefer a 12-hour AM/PM time.  

Thank you,
Chris
Bill PrewIT / Software Engineering ConsultantCommented:
Microsoft documents the Outlook Object Model online.  Here is a link to the Appointment object reference.

https://msdn.microsoft.com/EN-US/library/office/ff862177.aspx

~bp
Bill PrewIT / Software Engineering ConsultantCommented:
What do you localization settings look like, and are you running a foreign language version of Windows?

Control Panel, Region, Formats

Formats
~bp
damoncf1234Author Commented:
Bill,

Thank you for the replies - we're running win7 enterprise / US/American version.  

I can't get access to the system until Monday - I can check control panel on Monday - but it should be whatever the US default is.  

Thanks for the info about the outlook information.  

Chris
Bill PrewIT / Software Engineering ConsultantCommented:
One other thing to try, would be the Format() function explicitly telling the format you want.  Something like:

Format(olkAppt.Start, "mm/dd/yyyy Hh:Nn:Ss AM/PM")


Info on the formats can be found here: https://msdn.microsoft.com/en-us/library/office/gg251755.aspx

However, one other thing I notice is that you may be placing the formatted result into a cell in Excel, is that true?  If so then that brings a different variable to the situation.  The format of the cell will be applied to the value you place in there, even if you are formatting some other way.  You may have to either change the formatting of the cell, or specify the contents as text rather than letting Excel determine it to be date since it looks like one.

Try this small example to get an idea of this, in Excel.

Sub Tester()
    
    Dim datTest As Date
    datTest = #1/2/2003 1:01:02 PM#
    
    MsgBox Format(datTest, "mm/dd/yyyy Hh:Nn:Ss AM/PM")
    
    Cells(1, 1) = Format(datTest, "mm/dd/yyyy Hh:Nn:Ss AM/PM")
    
    Cells(2, 1) = Format(datTest, "mm/dd/yyyy Hh:Nn:Ss AM/PM")
    Cells(2, 1).NumberFormat = "mm/dd/yyyy hh:mm:ss AM/PM"
    
    Cells(3, 1) = "'" & Format(datTest, "mm/dd/yyyy Hh:Nn:Ss AM/PM")
    
End Sub

Open in new window

Notice that the MsgBox shows the result of the Format() function:

msgboxBut the display in the cells can be a lot different depending on cell formatting:

excel~bp
damoncf1234Author Commented:
Bill,

Thanks, I'll try using the format function on Monday and post back with results.  

The olkAppt.Start should contain the date and time - and using the format function, we should be able to display it exactly as needed, right?  

I looked at the other post with information about the various properties that you posted - and "Start" should include the start date and time, right?

Thanks,
Chris
Bill PrewIT / Software Engineering ConsultantCommented:
Yes, those objects as documented seem to be date time types, and will have date and time in it.  I will do a simple test here later today in Outlook and confirm.

~bp
damoncf1234Author Commented:
Thank you - I can't access my work system until Monday - but will try your latest suggestion and reply back.

Thanks
Chris
Bill PrewIT / Software Engineering ConsultantCommented:
Did a tiny test here, and those properties that you are referencing do seem to be normal date/time types, so that should be fine.

~bp
damoncf1234Author Commented:
Ok, thanks - I'll check on Monday - crossing fingers - thanks
David LeeCommented:
Bill,

I think he's doing this from VBScript, not VBA, so there's no FORMAT function.
David LeeCommented:
Chris,

I just tested the code I provided and it worked perfectly.  My test displays the result in a popup dialog-box.  This is what I get for an appointment that begins at 2:30pm.  

screenshot
Try displaying the formatted in a popup to see if the code is working properly.  If it is then I suspect the problem is Excel.  It must be modifying the date format in the spreadsheet.  If the formatted date is incorrect in the popup, then I'm at a loss to explain why.
Bill PrewIT / Software Engineering ConsultantCommented:
Ah yes, I see this is being done in VBS now, sorry I lost track of that.  Given that I suspect this is an Excel format problem, and the VBS FormatDateTime() function may even get the job done.

Throw in some of these and see if the format displayed differs from what Excel show:

excSht.Cells(lngRow, 2) = (FormatDateTime(olkAppt.Start,2) &" "& FormatDateTime(olkAppt.Start,3))
Wscript.Echo FormatDateTime(olkAppt.Start,2) & " " & FormatDateTime(olkAppt.Start,3)

Open in new window

~bp
damoncf1234Author Commented:
Yes, vbscript -- does it matter that it's being saved as a tab-delimited text file instead of an actual xls or xlsx file?  

It behaves like the second command I give it after a space in one cell gets ignored - if I don't put the space in there, I get the properly-formatted date and time - when I add a space to separate the time and date, it reverts back to a 24-hour time.  

Thanks again!
Bill PrewIT / Software Engineering ConsultantCommented:
I think the fact that you are placing it in a cell, before it gets saved as a CSV, means excel applied a format to it.

~bp
damoncf1234Author Commented:
Bill and BlueDevilFan,

Sorry for the delay - work's been crazy, and I'm getting ready for surgery soon.  

Found out yesterday that the 24-hour time worked fine for what we're using it for.  

Both of you gave some great advice, so I'll split the points.  Thank you again,

Chris
Bill PrewIT / Software Engineering ConsultantCommented:
Welcome, glad that was useful, good luck with your surgery.

~bp
damoncf1234Author Commented:
Thank you both - happy thanksgiving
David LeeCommented:
Happy Thanksgiving to you both!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.