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.

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

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 F1This 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

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

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.

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

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

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

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.

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

Thanks again, I'll give it a try.

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

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

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

Ask Your Question

Weekly Poll

Do you believe gaming is ready for Virtual Reality?

Discuss in The Lounge

Poll History