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:
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.
As soon as I put the space in, it puts double times in, and without the AM/PM...
I've also tried the obvious:
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
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))
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))
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
-- but it doesn't provide the correct format -- ex 11/18/2015 08:00 AMIf 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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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
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
ASKER
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.Sta rt,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.Sta rt,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
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.
(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.Sta
11/19/20158:15:00 AM (no space between the date and time, and extra zeros for seconds
If I use datDate &" "& FormatDateTime(olkAppt.Sta
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
ASKER
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:
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:
But as soon as I added the
It's almost like it doesn't recognize "Month" for some reason...
Up in the main script, I used
I've also tried to use datDate &" "& FormatDateTime(olkAppt.Sta rt,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
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
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
But as soon as I added the
Month(varDate) &
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)
to use the function... I've also tried to use datDate &" "& FormatDateTime(olkAppt.Sta
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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.
But the display in the cells can be a lot different depending on cell formatting:
~bp
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
Notice that the MsgBox shows the result of the Format() function:But the display in the cells can be a lot different depending on cell formatting:
~bp
ASKER
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
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
~bp
ASKER
Thank you - I can't access my work system until Monday - but will try your latest suggestion and reply back.
Thanks
Chris
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
~bp
ASKER
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.
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.
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.
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.
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:
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)
~bp
ASKER
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!
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
~bp
ASKER
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
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
~bp
ASKER
Thank you both - happy thanksgiving
Happy Thanksgiving to you both!
Open in new window
~bp