+ Reply to Thread
Results 1 to 10 of 10

VBA - Convert Date/Timestamp into American Date and Military Timestamp

  1. #1
    Registered User
    Join Date
    03-23-2011
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    50

    VBA - Convert Date/Timestamp into American Date and Military Timestamp

    Hello everyone,

    I receive a report that has a European date and time formatted as the below:

    2/10/2012 5:49

    I need this to be in American date and military time format.

    I have tried every single thing I can think of using custom formatting, text to columns, etc. When I MANUALLY format it using military time and then perform text to columns, it splits out the date, converts it to the American version (10/2/2012) and splits out the time and converts it to military time.

    However, not one single line of VBA code I've tried will do this. Every time it runs, it splits out the date and converts it to the American version (10/2/2012). However, the time is always coming out as a normal 12 hour clock cycle with an EXTRA column indicating AM/PM.

    Does anyone know how to fix this? I've spent three days on this.

    Thanks!

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: VBA - Convert Date/Timestamp into American Date and Military Timestamp

    Hello ryanmorris,

    Please explain what this "report" is. Are you referring to workbook you have received from an email or what?
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,860

    Re: VBA - Convert Date/Timestamp into American Date and Military Timestamp

    Hi Ryan,

    This is one of those situations where you will probably get a better and quicker response if you upload a sample workbook that displays the problem. Also include a sample of exactly how you want that date (and time) formatted after the values are converted to the format you want.

    Lewis

  4. #4
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,441

    Re: VBA - Convert Date/Timestamp into American Date and Military Timestamp

    This works for me

    Please Login or Register  to view this content.
    replace Now with your source cell eg

    Please Login or Register  to view this content.

    For completeness I checked the cell format.

    It is formatted as custom using the formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by mehmetcik; 04-30-2015 at 05:46 PM.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  5. #5
    Registered User
    Join Date
    03-23-2011
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: VBA - Convert Date/Timestamp into American Date and Military Timestamp

    Thank you everyone for taking the time to reply. I'm going to provide a slight update, but I'd still like to know if there's a way to fix this using VBA that is not crazy-complex.

    Apparently, there is a problem in Excel with regards to files containing dates that differ in regional zone from your own computer. The dates I'm extracting are being produced in the European format while my computer is in American. What happens is that, Excel recognizes any European date beginning with a 1 through 12 (that part of the date being a day) but for any date where the day is the 13th through the 31st, it suddenly cannot recognize it as a date and formats it as text. From what I can tell there is no solution for this, but I trust people on here have likely encountered this before and found some sort of solution.

    I've attached a sample workbook. Column A is the data as it exists after exporting the report. Column B shows how each row is formatted (formatting that exists already - I did not apply this). Columns C and D show how the date and time should look.

    Thank you again everyone!

    Example.xlsx

  6. #6
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,860

    Re: VBA - Convert Date/Timestamp into American Date and Military Timestamp

    Hi Ryan,

    Try the attached copy of your file which uses the VBA User Defined Function named 'DateManipulation'. If it doesn't do exactly what you want, it should get us started in the right direction.

    Please Login or Register  to view this content.
    Lewis

  7. #7
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: VBA - Convert Date/Timestamp into American Date and Military Timestamp

    Hello RyanMorris,

    Here is another version. This forces the workbook to be in US English date and time format. I have added the macro and a button to run it in the attached workbook.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    03-23-2011
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: VBA - Convert Date/Timestamp into American Date and Military Timestamp

    Thanks LJMetzGer, this is phenomenal!

    Leith Ross, yours was great too, but I noticed it only converts those items in cells that were formatted as General. It leaves the rest of the dates alone. If I were to do a text-to-columns on this data now, it would reverse all of the data keeping it out of sync. You may want to look into that in the event someone else wants to use your solution.

    Thank you again everyone!

  9. #9
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: VBA - Convert Date/Timestamp into American Date and Military Timestamp

    Hello ryanmorris,

    Thanks for letting me know what you found. I have updated the macro. It will change each cell's format to match the desired output. Even if the user changes the format at a later date, when the macro runs it will be changed back.

    Here is the new macro code.

    Please Login or Register  to view this content.
    NOTE: I just tried the Text to Columns function and it is not affected! I have on idea why that would be. Use VBA instead and space yourself the pain of bad software design.
    Last edited by Leith Ross; 05-01-2015 at 03:28 PM.

  10. #10
    Registered User
    Join Date
    03-23-2011
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: VBA - Convert Date/Timestamp into American Date and Military Timestamp

    Yep, this works great now too. Thank you so much, again, everyone!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Date/Timestamp difference in HH:MM
    By JP Romano in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-21-2014, 01:54 PM
  2. [SOLVED] Convert a cell with a timestamp in it to just the date to compare to another date
    By Tanyab in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-17-2013, 05:41 AM
  3. Convert Timestamp vaule to date
    By dpatel in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-23-2010, 06:47 AM
  4. date without timestamp
    By r.vasikaran in forum Excel General
    Replies: 2
    Last Post: 05-13-2006, 07:10 AM
  5. Convert Unix timestamp to Readable Date/time
    By Emily in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-26-2006, 08:00 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1