Link to home
Start Free TrialLog in
Avatar of damoncf1234
damoncf1234

asked on

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
Avatar of Bill Prew
Bill Prew

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
ASKER CERTIFIED SOLUTION
Avatar of David Lee
David Lee
Flag of United States of America image

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 damoncf1234

ASKER

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
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
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
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
SOLUTION
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
What do you localization settings look like, and are you running a foreign language version of Windows?

Control Panel, Region, Formats

User generated image
~bp
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
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:

User generated imageBut the display in the cells can be a lot different depending on cell formatting:

User generated image~bp
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
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
Thank you - I can't access my work system until Monday - but will try your latest suggestion and reply back.

Thanks
Chris
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
Ok, thanks - I'll check on Monday - crossing fingers - thanks
Bill,

I think he's doing this from VBScript, not VBA, so there's no FORMAT function.
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.  

User generated image
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.
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
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!
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
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
Welcome, glad that was useful, good luck with your surgery.

~bp
Thank you both - happy thanksgiving
Happy Thanksgiving to you both!