Excel-Time difference between two times

August 6, 2010 at 03:09:05
Specs: Windows Vista
hi, i am trying to put together an event spreadsheet in excel that include a realtime running clock and multiple time to next event (count down timer) reference the running clock
i have a realtime running clock in VB but not very successful with the count down timer or time to event part
can anyone assist?
many thanks in advance

See More: Excel-Time difference between two times

Report •

August 6, 2010 at 04:02:04

As you haven't provided any specifics, such as where your real-time clock is (which cell or cells does it display in) and what your VBA code is actually placing in a cell or cells, it's pretty difficult to provide an answer.

In general terms the difference between two times in say cells A1 (time now) and A2 (finish time - a time in the future) is A2-A1. If the formula A2-A1 is in cell A3, then formatting it as [h]:mm will display the time difference as hours and minutes even if the difference is greater than 24 hours (i.e. >1 day)

Next you need to consider whether the time now and finish time are either both times only with no date information or both time+date information, or one contains time + date and the other contains time only.

If both are the same, then the simple A2-A1 formula works.
If A1 is 11AM today and finish is 11:30AM tomorrow the difference is 24:30 using [h]:mm format.
If A1 is 11AM on 11 August 2010 it stores the value 40396.4583 (approx.) , even if formatted as hh:mm
If finish time in A2 is 12:00 (entered by typing in 12:00), it has a zero date and holds 0.4792, and thus A2-A1 is negative.
Use this to get the decimal/time part of A1 to compare with A2: =A2-MOD(A1,1) - which compares times only and the difference 11AM on 11 Aug 2010 and 12:00 is 1:00.

Finally as you are doing a count down you need someway to stop a negative result, once you get to zero, the result needs to remain zero.
With Excel times, if you have A2-A1 and A1 is later than A2 you will get a cell filled with ######.
So use this in A3: =IF(A2<=MOD(A1,1),0,A2-MOD(A1,1))

If you need more help, please provide details of what you have done, and include the VBA code that creates your times.


Report •

August 6, 2010 at 04:14:24
hi, sry i pretty new at this and do not know how to attach my document. so here's the VB for the running clock

the code for the module>

Dim SchedRecalc As Date
Sub Recalc()
Dim wbk As Workbook
Dim ws As Worksheet

Set wbk = ThisWorkbook
Set ws = wbk.Sheets("Sheet1")

ws.Range("C3").Value = Format(Now, "ddd dd mmm yy")
ws.Range("C4").Value = Format(Time, "hh:mm:ss")

Call SetTime
End Sub
Sub SetTime()
SchedRecalc = Now + TimeValue("00:00:01")
Application.OnTime SchedRecalc, "Recalc"
End Sub

Sub Disable()
On Error Resume Next
Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc", Schedule:=False
End Sub

the time will run in cell c3 and c4 etc
when i tried insert a formula to find diff betn the running time and the next event. it shows me a #value

please advice, thanks.

Report •

August 6, 2010 at 04:49:04

C3 contains a date only and no time information (time=0, so technically midnight).
C4 contains time information only.

If you put a finish time in C5, then
enter this in C6: =IF(C4>=C5,0,C5-C4)
Format cell C6 hh:mm:ss
(with no date information, the time difference will never be >24 hours).

The above formula displays an hours, minutes and seconds count down to zero


Report •

Related Solutions

August 6, 2010 at 05:26:27
looking good but yes, i need to include date as well cos some event will be the next day. hence i will need to show the diff in hh:mm:ss

i include date into the vb and now c4 shows the the date as well

ws.Range("C4").Value = Format(Now, "ddd dd mmm yy hh:mm:ss")

the next event column i also include date and time

i use your formula above but i does not work

your advice please.

Report •

August 6, 2010 at 09:01:17

You haven't said how it doesn't work - what happens. Give examples of values in C4 and D4 and and what the result of the formula is.

You now have a combined date and time in cell C4, and it looks from your last response that you have a combined date and time in the next column, (cell D4 I presume)

I modified the formula I proposed, to point to cells C4 (current time) and D4 (finish time): =IF(C4>=D4,0,D4-C4)
with 06/Aug/2010 11:54 in C4 and
07/Aug/2010 12:54 in D4,
the formula returned this: 25:00:00
when formatted as [h]:mm


Report •

August 6, 2010 at 19:57:29
hi Mike,

i was still trying out the formulas and codes, and finally got the work around solution early this morning.
instead of subtracting the time difference with the next event time (future time) with the running clock, which does not work. for some reason it does not recognize the cell for the running clock.
Solution: subtract the time diff betwn the next event (future time) and the now() function.
it works, cos when i run the VB macro for the worksheet, it also recalculate the whole worksheet.
thanks again i really appreciate your prompt assists, and until the next time, cheers!


Report •

October 29, 2010 at 16:59:30
Hello Humar, or anyone else that may be able to help,

I've created an excel worksheet, and I'm trying to track the time that it takes to finish a project. My problem is that I need to see a running (real-time) clock that shows the days elapsed between the time the person starts the project "Start Date" (signified by them entering a start date, month/day/yr), and the time that they finish the project "Completion date." (signified by the person entering an End date, month/day/yr).

Once the person completes the project, and enters the "Completion date." The timer needs to stop, and retain the number of elapsed days.

It would be nice if the real-time counter is formatted to display in Days, Hours, and minutes. I also want to avoid getting error messages when either cell is empty. Is this something that you can help me with? Thanks.

Report •

January 24, 2011 at 04:25:13
I want to made the difference of two times like I wrote 1430 hours in cell a1 1800 hours in b1. Now in cell c1 I want to see the difference of 03:30 hours. How can I do this job please give a details.

Report •

Ask Question