Converting Time from 12-Hour to 24-Hour Format

[CE101]

I am manipulating some data in an Excel file.

One of the fields contains Time in the common 12-hour format (HH:MM:SS am/pm). Example: 10:28:30 PM.

I want to convert it to 24-hour format. Example: 22:28:30.

I haven't seen any UDFs for this in the AutoIt library, so I set about to do it myself.

It shouldn't be too difficult. Parse the string into a four element array: hour, month, day, am/pm.

And if element #4 is "pm" add 12 to the hour.

$Time1 = _ExcelReadCell($oExcel, $Row , $Col_Time )
$Time    = StringSplit($Time1, " :")
$Hour    = $Time[1]
$Minute  = $Time[2]
$Second  = $Time[3]
$AM_PM   = $Time[4]
If $AM_PM = "PM"  AND  $Hour <> "12"   then
    $Hour = $Hour + 12 
EndIf
$Time2 = $Hour & ":" & $Minute & ":" & $Second

The problem I'm having is that after reading the Excel file, $Time1 does not contain the expected value.

Looking at the data in Excel, the value of Time in the first row is10:28:30 PM .

However when I do _DebugReportVar I see the following:

{Double} -> $Time1 = 0.936458333333333

In Excel, the number-format for the Time column is "Custom - h:mm:ss am/pm"

Question: How do I force $Time1 to remain a String rather than a DoubleWord ?

Any suggestions would be greatly appreciated. Sample code would be even better.

Gary

[enaiman]

It should work - except if the data is only "displayed" as 10:28:30 PM" which it is

Change the format of the cell to "General" or "Number and see what it displays ... does "0.936458333333333" look familiar? it should, because it is what it stands for that specific time.

"What you see" in Excel is not always "what you get"

About how to convert this in a real time format? No idea, for me, the number looks like a percentage of a full 24 hours; some research should point you to the right conversion formula.

Good luck.

Edit: Actually I felt good enough to write myself such a function, to convert the time in decimal format to a proper 24 hours or 12 hours format, here it comes:

MsgBox(0, "time", _ExcelDecToTime(0.936458333333333))

Func _ExcelDecToTime($dec_value, $format = 0)   ;format=0 -> 24 hours, format=1 -> 12 hours
    $time_sec = $dec_value*86400
    $hours = Int($time_sec/3600)
    If $hours < 10 Then $hours = "0"&$hours
    $minutes = Int(Mod($time_sec,3600)/60)
    If $minutes < 10 Then $minutes = "0"&$minutes
    $seconds = Round(Mod($time_sec,60))
    If $seconds < 10 Then $seconds = "0"&$seconds
    Switch $format
        Case 0      ;24 hours format
            Return $hours&":"&$minutes&":"&$seconds
        Case 1      ;AM/PM format
            If $hours > 12 Then
                If Number($hours) = 24 Then 
                    Return "12:"&$minutes&":"&$seconds&" AM"
                Else
                    Return $hours-12&":"&$minutes&":"&$seconds&" PM"
                EndIf
            Else
                If Number($hours) = 0 Then 
                    Return "12:"&$minutes&":"&$seconds&" AM"
                Else
                    Return $hours&":"&$minutes&":"&$seconds&" AM"
                EndIf
            EndIf
        Case Else
            MsgBox(16, "Flag error", $format&" is not a valid option")
    EndSwitch
EndFunc

I'm not sure it is 100% functional, my tests looked OK. If something is wrong - you can easily correct the logic.

Edit 2: if you are changing the data format in the same excel file, you might consider having a look at "_ExcelNumberFormat" function from here: http://www.autoitscript.com/forum/index.php?showtopic=34302

Edited July 1, 2010 by enaiman

[CE101]

Hi enaiman:

Thank you for replying and thank you for the code!

> It should work - except if the data

> is only "displayed" as 10:28:30 PM" which it is

> Change the format of the cell to "General"

> or "Number and see what it displays ...

> does "0.936458333333333" look familiar?

> it should, because it is what it stands for that specific time.

Yes I see... "10:28:30 PM" is just a display representation of the following number stored in Excel: "0.936458333333333".

I wonder how the two relate?

[jchd]

The relation is called "Julian day". It's a positive count (as a floating point value) of time since noon on November 24, 4714 B.C. (the start of the proleptic Gregorian calendar) in Greenwich. In that convention, 1.0 = 24 solar hours. The code given by mvGulik (ooops, not him) enaiman explains all the fractional part to 12/24h time display.

Edit: correct misattribution, my bad!

Edited July 5, 2010 by jchd

[Spiff59]

Edit: Actually I felt good enough to write myself such a function, to convert the time in decimal format to a proper 24 hours or 12 hours format, here it comes:

There's no such thing as hour "24", isn't it 0-23?

Also 12:20 in 24-hour format would convert to 12:20 PM, but would be returned as 12:20 AM in the existing code.

I think this would work better for the 24-to-12 part of your conversion:

Case 1      ;AM/PM format
            If Not Mod($hours, 12) Then $hours += 12 ; If 0 or 12 then add 12
            If $hours > 12 Then
                Return $hours - 12 & ":" & $minutes & ":" & $seconds & " PM"
            Else
                Return $hours & ":" & $minutes & ":" & $seconds & " AM"
            EndIf

Edited July 3, 2010 by Spiff59

[enaiman]

There's no such thing as hour "24", isn't it 0-23?

Also 12:20 in 24-hour format would convert to 12:20 PM, but would be returned as 12:20 AM in the existing code.

I think this would work better for the 24-to-12 part of your conversion:

Case 1      ;AM/PM format
            If Not Mod($hours, 12) Then $hours += 12 ; If 0 or 12 then add 12
            If $hours > 12 Then
                Return $hours - 12 & ":" & $minutes & ":" & $seconds & " PM"
            Else
                Return $hours & ":" & $minutes & ":" & $seconds & " AM"
            EndIf

Just answered your question in my Example Scripts thread. I think I am aware that there are only 24 hours in a day ...

I've included both these values because I thought the values in the cell might be between 0 and 1 INCLUDING these 2 values ...

Well, Excel translates 0 as 12:00:00 AM and 1 translate to "1/01/1900 12:00:00 AM" which is quite weird to me, anyway, I'll remove the 1 from possible values.

Here is the updated code:

MsgBox(0, "24 Hour Time format", _ExcelDecToTime(0.936458333333333))
MsgBox(0, "12 Hour Time format", _ExcelDecToTime(0.5, 1))

Func _ExcelDecToTime($dec_value, $format = 0)   ;format=0 -> 24 hours, format=1 -> 12 hours
    $time_sec = $dec_value*86400
    $hours = Int($time_sec/3600)
    If $hours < 10 Then $hours = "0"&$hours
    $minutes = Int(Mod($time_sec,3600)/60)
    If $minutes < 10 Then $minutes = "0"&$minutes
    $seconds = Round(Mod($time_sec,60))
    If $seconds < 10 Then $seconds = "0"&$seconds
    Switch $format
        Case 0      ;24 hours format
            Return $hours&":"&$minutes&":"&$seconds
        Case 1      ;AM/PM format
            Switch Number($hours)
                Case 13,14,15,16,17,18,19,20,21,22,23
                    Return $hours-12&":"&$minutes&":"&$seconds&" PM"
                Case 12
                    Return $hours&":"&$minutes&":"&$seconds&" PM"
                Case 1,2,3,4,5,6,7,8,9,10,11
                    Return $hours&":"&$minutes&":"&$seconds&" AM"
                Case 0
                    Return "12:"&$minutes&":"&$seconds&" AM"
                Case Else
                    MsgBox(16, "Hour Error", $hours&" is not between 0 and 24")
            EndSwitch
        Case Else
            MsgBox(16, "Flag error", $format&" is not a valid option")
    EndSwitch
EndFunc

[jchd]

There is a pseudo-explanation for the Excel behavior. Julian date count time from noon on November 24, 4714 B.C., i.e. -4714/11/24 12:00:00 but it looks like Excel project was reluctant to use a start date that far in history, and they seem to have decided that the second day would be 01/01/1900 (and they goofed for the first day which should display 31/12/1899 instead of a simple 0, by their logic). That's typical of the bad choices made in Excel and other MS products, though it might be for compatibility reason with previous broken spreadsheets.

Edit: I just noticed incidently that it looks like (can't be sure) that Delphi displays 1899-12-30 00:00:00. for DateTime stamps having a value of 0.

Edited July 5, 2010 by jchd

[CE101]

Hi jchd, enaiman, Spiff59:

Thank you very much for the code and explanations you've all added to this thread since July2.

I'm afraid I haven't seen your material till now - I forgot to set "email notification" on my posting.

Sorry about that.

[MrCheese]

Hi Guys

thanks for your work!

I used this function, but I was getting an output of 08:60 in the case of reading a cell: 08:00 am

I tried rounding this, which rounded up to 09:00 but I wasn't able to determine the logic on why it was rolling up.

I ended up cell reading from excel individually using the 'value shown' and not putting it into an array.

I know this was years ago but I wasn't able to determine a more suitable process.

cheers

[Juvigy]

And why dont you do it in excel directly? Here is how a date and time formatted in 12 and 24h looks like when you execute the code:

ConsoleWrite($oExcel.Application.ActiveCell.Value &@CRLF)
ConsoleWrite($oExcel.Application.ActiveCell.text &@CRLF)

And here is the result:

Format1:

43373
12:00:00 AM

Format2:

43373
0:00