# 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 partcan anyone assist?many thanks in advancejoe

See More: Excel-Time difference between two times

#1
August 6, 2010 at 04:02:04
 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:mmIf 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

Report •

#2
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 clockthe code for the module>Dim SchedRecalc As DateSub Recalc()Dim wbk As WorkbookDim ws As WorksheetSet wbk = ThisWorkbookSet ws = wbk.Sheets("Sheet1")ws.Range("C3").Value = Format(Now, "ddd dd mmm yy")ws.Range("C4").Value = Format(Time, "hh:mm:ss")Call SetTimeEnd SubSub SetTime()SchedRecalc = Now + TimeValue("00:00:01")Application.OnTime SchedRecalc, "Recalc"End SubSub Disable()On Error Resume NextApplication.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc", Schedule:=FalseEnd Subthe time will run in cell c3 and c4 etcwhen i tried insert a formula to find diff betn the running time and the next event. it shows me a #valueplease advice, thanks.

Report •

#3
August 6, 2010 at 04:49:04
 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 zeroRegards

Report •

Related Solutions

#4
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:ssi include date into the vb and now c4 shows the the date as wellws.Range("C4").Value = Format(Now, "ddd dd mmm yy hh:mm:ss")the next event column i also include date and timei use your formula above but i does not work your advice please.

Report •

#5
August 6, 2010 at 09:01:17
 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 and07/Aug/2010 12:54 in D4,the formula returned this: 25:00:00when formatted as [h]:mmRegards

Report •

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

Report •

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

#8
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 •