Solved How to create a Real-time counter in Excel

October 29, 2010 at 17:06:04
Specs: Macintosh or PC

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.


See More: How to create a Real-time counter in Excel

Report •


#1
October 29, 2010 at 19:29:13

Put your Start Date in cell B1
Put your End Date in cell D1
Put the below formula in F1

(This may be a bit of overkill, but you might like it, it will update every time your worksheet updates.)

=IF(D1="",DATEDIF(B1,NOW()-(MOD(B1,1)>MOD(NOW(),1)),"ym")&" months, "&DATEDIF(B1,NOW()-(MOD(B1,1)>MOD(NOW(),1)),"md")&" days, ",DATEDIF(B1,D1-(MOD(B1,1)>MOD(D1,1)),"ym")&" months, "&DATEDIF(B1,D1-(MOD(B1,1)>MOD(D1,1)),"md")&" days, ")

It's all one long formula, just copy & paste.

If you also want the hours & min, the formula gets a lot longer.

MIKE

http://www.skeptic.com/


Report •

#2
October 29, 2010 at 20:09:05
✔ Best Answer

Here is the complete formula, it will diaplay

Years, Months, Days, Hours, Minutes, Seconds.

Again,
Put your Start Date in cell B1
Put your End Date in cell D1
Put the below formula in F1

This is all one formula:

=IF(D1="",DATEDIF(B1,NOW()-(MOD(B1,1)>MOD(NOW(),1)),"y")&" years, "&DATEDIF(B1,NOW()-(MOD(B1,1)>MOD(NOW(),1)),"ym")&" months, "&DATEDIF(B1,NOW()-(MOD(B1,1)>MOD(NOW(),1)),"md")&" days, "&TEXT(MOD(NOW()-B1,1),"hh "" hours, "" mm "" minutes, and "" ss ""seconds"""),DATEDIF(B1,D1-(MOD(B1,1)>MOD(D1,1)),"y")&" years, "&DATEDIF(B1,D1-(MOD(B1,1)>MOD(D1,1)),"ym")&" months, "&DATEDIF(B1,D1-(MOD(B1,1)>MOD(D1,1)),"md")&" days, "&TEXT(MOD(D1-B1,1),"hh "" hours, "" mm "" minutes, and "" ss ""seconds"""))

You'd best copy and paste.

MIKE

http://www.skeptic.com/


Report •

#3
October 29, 2010 at 20:35:54

Thanks a million Mike! I'll give it a try and let you know how it works. I really appreciate your help.

Robert


Report •

Related Solutions

#4
October 29, 2010 at 21:05:46

Mike,

I'm a little new at this and I'm working on a Mac. Can you tell me what's supposed to be in F1? F1 on a Mac is for adjusting display features. Thanks.


Report •

#5
October 29, 2010 at 21:08:34

Also, which cell/cells do I paste the formula into? Thanks.

Report •

#6
October 29, 2010 at 21:23:10

Mike, I pasted the formula in cell D1, and I'm getting in error message that says that the formula is a circular reference. Any suggestions?

Robert


Report •

#7
October 29, 2010 at 21:52:56

Mike,

I was able to get the formula to work, and that's fantastic! However when both B1 and D1 are empty, instead of F1 showing 0 days, it shows a really large number like 106 years. If I can fix that little problem, then I'm in great shape. Also, the year, month, day, hour, and seconds is probably a little over-kill. I'll try to cut it down to just the number of days.

How do I tell the formula to return 0 days when both B1 and D1 are empty? Thanks.

Robert


Report •

#8
October 30, 2010 at 09:06:58

Drop-down Calendar
Mike,

Thanks for the help with the timer. I have one last request. Do you have the code for creating a drop-down calendar within cells? I have a lot of date fields in my spreadsheet and it would be great if I could have a drop-down calendar for each field.

I apologize for all of the dumb questions in my earlier responses. I'm a beginner. The problems that I previously had were likely because I was using the code on my Mac instead of a PC. This is a work project, so it will be installed on a PC. Thanks again for all of your help.


Report •

#9
October 30, 2010 at 10:45:22

Glad you got everything worked out.

I apologize for all of the dumb questions
There is no such thing as a dumb question.

I have never had the need for a dropdown calendar, but found this, which looks like it might be what you need.

http://danielcurran.com/instruction...

MIKE

http://www.skeptic.com/


Report •

#10
October 30, 2010 at 11:07:20

Thanks again, I'll give it a try.

Report •

#11
October 30, 2010 at 19:52:38

Rob2233 (and Mike),

Since the question about the Drop Down calendar is not related to the Real Time Counter question, it should have been asked (and answered) in a separate thread, with it's own subject line.

There are 2 reasons for this:

1 - There may be members who are not following the Real Time Counter thread who may have suggestions. They will never see the question because it is buried in an unrelated thread. Not that there is anything wrong with Mike's suggestions, but you might miss out on some other options.

2 - Someone else may be interested in a drop down calendar in the future and it should be in the archives under it's own subject line, not buried inside an unrelated thread, making it harder to find.

Please try to keep that in mind the next time you have multiple questions that aren't related to each other.

Thanks!

DerbyDad03
Office Forum Moderator


Report •

#12
February 27, 2011 at 21:35:54

Dear Mike thanx for your formula, but i need this formula include "Year" because i am working in an IBM authorized company, so i need to prepare a warranty sheet to show me that how many days, months and years of warranty of specific Item is remain,

regards
Zubair Ehsas


Report •

#13
February 28, 2011 at 11:48:25

Zubair, please ask your question it a New thread, and include more detail as to what you need.
A before and after example is always helpful.

MIKE

http://www.skeptic.com/


Report •


Ask Question