Clock Formula Assistance

[Bimmy]

Hello All,

Not sure if I'm posting in the right section as my question also involves using macro to display time.

Below is the code that displays current local time in Cell A1.

Public Declare Function SetTimer Lib "user32" ( _
ByVal HWnd As Long, ByVal nIDEvent As Long, _
ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long

Public Declare Function KillTimer Lib "user32" ( _
ByVal HWnd As Long, ByVal nIDEvent As Long) As Long

Public TimerID As Long, TimerSeconds As Single, tim As Boolean
Dim Counter As Long

'~~> Start Timer
Sub StartTimer()
    '~~ Set the timer for 1 second
    TimerSeconds = 1
    TimerID = SetTimer(0&, 0&, TimerSeconds * 1000&, AddressOf TimerProc)
End Sub

'~~> End Timer
Sub EndTimer()
    On Error Resume Next
    KillTimer 0&, TimerID
End Sub

Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, _
ByVal nIDEvent As Long, ByVal dwTimer As Long)
    '~~> Update value in Sheet 1
    Sheet1.Range("A1").Value = Time
End Sub

Using below formula to display the time in Cell B2 after subtracting 05:00 Hrs

=A1-(5/24)

Looking for a formula that will subtract 05:30mns from Cell A1 and display time in Cell B2

 

[sdsurzh]

=A1-(5.5/24)

try this

 

[NARAYANK991]

Hi ,

Just =A1 - "05:30" will do.

See the attached file.

Narayan

 

[Bimmy]

Hi,

I have used both the formulas suggested.

The formula is not reflecting time, instead it is showing #########

I have formatted the respective cell but unsuccessful.

 

[NARAYANK991]

Hi ,

A negative value will be displayed as #######.

If you want that this should be displayed correctly , use the following formula :

=IF(A1 - "05:30" < 0, A1 + 1 - "05:30", A1 - "05:30")

Narayan

 

[Bimmy]

Narayank991... thanks for the explanation regarding negative value.

The formula works... can you explain what it does.

 

[NARAYANK991]

Hi ,

In Excel , a time value is a decimal number between 0 and 0.999988425925926 , where 0 is one midnight , and 0.999988425925926 representing 23:59:59

So , when we subtract say 5:30 from a time such as 03:00 , we will end up with a negative number , since the decimal value corresponding to 05:30 is greater than the decimal value corresponding to 03:00

So what the formula is doing is to check whether the result of subtracting 05:30 from a given time is negative , and if so , add 1 to the given time and then subtract 05:30 ; the result will now be correct , and display 21:30

Narayan