Excel time format

[enaiman]

The starting point is in this thread: http://www.autoitscript.com/forum/index.php?showtopic=116573

That made me realize that Excel "has a mind of its own" and the content of a cell displaying the time might not be what you get when you read that cell.

I did a quick search in this forum for "Excel" and "Time" tags and couldn't see a similar post. I suspect there might be one or more which I couldn't find because it is hard to believe that during several years nobody had this problem ... well, here it comes my approach: a function returning 24 hours/12 hours time.

MsgBox(0, "24 Hour Time format", _ExcelDecToTime(0.936458333333333))
MsgBox(0, "12 Hour Time format", _ExcelDecToTime(0.936458333333333, 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

EDIT: @Spiff59 - thanks for input - indeed 12:00 PM was never returned. Very nice piece of code, unfortunately it was returning 24:00 PM so I did a bit of work and changed the if-else statements with a more explicit Switch/EndSwitch

It is not very elegant but it can't fail

Edited July 4, 2010 by enaiman

[Spiff59]

You have tests for "hours = 0" and for "hours = 24". That's a spread of 25 hours.

You also have two tests that return a "12 AM" result, and no way for a "12 PM" to be returned.

I think the following would work better as your "24-to-12" 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

[enaiman]

@Spiff59

Thank you for your input - you were right 100%

I have changed the code to give the proper return.