Jump to content
Sign in to follow this  
enaiman

Excel time format

Recommended Posts

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 :blink:

It is not very elegant but it can't fail ;)

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

Share this post


Link to post
Share on other sites
enaiman

@Spiff59

Thank you for your input - you were right 100% :blink:

I have changed the code to give the proper return.


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

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  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.