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

joe

Hi, 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.

Regards

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 WorksheetSet 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 SubSub Disable()

On Error Resume Next

Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc", Schedule:=False

End Subthe 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 #valueplease advice, thanks.

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

Regards

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.

Hi, 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]:mmRegards

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

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.

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.

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History