Jump to content
Sign in to follow this  
CE101

Converting Time from 12-Hour to 24-Hour Format

Recommended Posts

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

Share this post


Link to post
Share on other sites
enaiman

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

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 by enaiman

SNMP_UDF ... for SNMPv1 and v2c so far, GetBulk and a new example script

wannabe "Unbeatable" Tic-Tac-Toe

Paper-Scissor-Rock ... try to beat it anyway :)

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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 by jchd

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
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 by Spiff59

Share this post


Link to post
Share on other sites
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

SNMP_UDF ... for SNMPv1 and v2c so far, GetBulk and a new example script

wannabe "Unbeatable" Tic-Tac-Toe

Paper-Scissor-Rock ... try to beat it anyway :)

Share this post


Link to post
Share on other sites
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 by jchd

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×