Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: Convert date and time to military time

  1. #1
    Join Date
    2nd May 2011
    Posts
    71

    Convert date and time to military time

    Hello,

    Hope some can help me out with this issue.

    I have a column in a spread sheet that is populated with date/time from an external application in the format "mm/dd/yyyy hh:mm" for example value in cell "B1" is "5/7/2011 15:45". I need only 1545. Using excel formula, I am able to convert in the adjacent cell "C1" using the formula =text(B1,"hhmm").

    I would however like to replace the contents in the original column "B:B" to hhmm using vba code. Below is the code that I have come up with but it does not seem to work. The macro returns #NAME? throughout column B.

    Thanks for any help in advance.


    VB:
    Sub arrivalTimes() 
         
         
        Dim sta As String 
         
         
        With ActiveSheet 
            Range("C1").Select 
            Do 
                sta = "=text(ActiveCell, ""hhmm"")" 
                ActiveCell.Value = sta 
                ActiveCell.Offset(1, 0).Select 
            Loop Until IsEmpty(ActiveCell.Offset(0, 0)) 
        End With 
         
         
         
         
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,042

    Re: Convert date and time to military time

    For instance:

    VB:
    Sub arrivalTimes() 
        Columns(2).NumberFormat = "hhmm" 
        For Each Rng In Columns(2).SpecialCells(2, 1) 
            Rng.Value = Rng.Value - Int(Rng.Value) 
        Next 
    End Sub 
    
    
    Watch out, it will overwrite values in column B. Take a backup of the file before testing.
    Regards,

    Wigi

    Excel MVP 2011, 2012, 2013



    For more Excel memes: visit http://www.wimgielis.be ==> English articles ==> Excel memes

    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  3. #3
    Join Date
    2nd May 2011
    Posts
    71

    Re: Convert date and time to military time

    Dear Wigi,

    Thank you for the code. I have just tried it and get a "Run Time error 1004" "no cells were found".
    Also I noticed in the code no Dim statements. Is that the reason for the error? im not sure as im a novice at VBA. thanks in advance for your time.

    Regards,
    XL Factor

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,418

    Re: Convert date and time to military time

    Change
    VB:
    sta = "=text(ActiveCell, ""hhmm"")" 
    
    
    to
    VB:
    sta = Format$(ActiveCell, "hhmm") 
    
    
    or
    VB:
    sta = Evaluate("text(" & ActiveCell.Address & ", ""hhmm"")") 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    2nd May 2011
    Posts
    71

    Re: Convert date and time to military time

    Dear Jindon,

    Thank you for your quick response. I have tried both of your above suggestions, but they dont work. The code changes all the dates to a different date and all the times to 0:00 in column B. Thanks again for all your help, I really appreciate it.

    Regards,
    XL Factor.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,418

    Re: Convert date and time to military time

    If the value in the cell is serial date with time then it should work.

    I have tested here.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    1st September 2010
    Posts
    7,849

    Re: Convert date and time to military time

    Try expanding the columns widths.... if the 'dates' align left then they're strings and you'll need to do more processing. If they align right then Jindons' solution should work.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    2nd May 2011
    Posts
    71

    Re: Convert date and time to military time

    Dear Jindon,

    I am attaching a sample workbook with the data and code in it. I am unable to get it to work. Please note the date/time column in the attachment is in column C in the attachment. Any help is greatly appreciated.
    Thank you again in advance for your time and efforts.

    Regards,
    XL Factor.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,418

    Re: Convert date and time to military time

    try
    VB:
    sta = Format$(ActiveCell, "hhmm") 
    Activecell.numberformat = "@" 
    ActiveCell = sta 
    
    
    Or you could do it with no loop
    VB:
    Sub test() 
        Dim x 
        With Range("c1", Range("c" & Rows.Count).End(xlUp)) 
            x = Evaluate("if(" & .Address & "<>"""",text(" & .Address & ",""hhmm""),"""")") 
            .NumberFormat = "@" 
            .Value = x 
        End With 
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Convert Date & Time As Text To Real Date & Time
    By [sHaQ] in forum EXCEL HELP
    Replies: 5
    Last Post: March 3rd, 2012, 00:06
  2. Replies: 3
    Last Post: February 21st, 2008, 19:18
  3. Convert Standard Time In Military Time
    By Kristi in forum EXCEL HELP
    Replies: 6
    Last Post: June 21st, 2007, 23:59
  4. Convert from Military time to Standard ET
    By Archie in forum EXCEL HELP
    Replies: 3
    Last Post: February 9th, 2005, 09:57
  5. Military Time
    By maxflia10 in forum OPEN SOURCE: Hey! That is Cool!
    Replies: 0
    Last Post: March 29th, 2003, 04:11

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
  •  
porno