I have copied your formula for

YEARS,MONTH,DAYS,HOURS,MINUTES,SECONDS

I altered the start date and end date to

C3 & C4 and put the formula in A5.My results are;

29 years,4 months, 29 days,00 hours, 00 minutes, 00 seconds.I have checked and double checked my input.

can you see where I'm going wrong?Here's a link:http://www.computing.net/answers/office/how-to-create-a-realtime-counter-in-excel/12478.html

message edited by Adage

✔ Best Answer

23-Feb-85 to =TODAY()There is no need to use the =TODAY() function with the formula,

if you leave theEnd Dateemptyit will automatically use

Today's date to calculate the length of time.So with 02/23/1985 and NO time, just the date, in cell C3

and cell C4 left blank you should get:29 years, 5 months, 0 days, 12 hours, 46 minutes, and 55 seconds

The hours and minutes and second are calculated using 00:00:00 or

Midnight as the default time.The

End Dateis used toLock Inthe length of time, so no further

calculations occur.EDIT ADDED:

If you enter

Both Dateswith NO TIME you get 00:00:00 because you are subtracting midnight from midnight which equals zero.

MIKE

message edited by mmcconaghy

What the heck are you talking about? How about a link to the formula and some details on what you're trying to do with it. Don't forget to preorder your Hatch green chili for this fall. Many vendors ship world-wide.

message edited by DAVEINCAPS

It's hard for us to tell you what you did wrong (if anything) since you only gave us the output. Unless we know the input we have no way of knowing what's "wrong" with:

29 years,4 months, 29 days,00 hours, 00 minutes, 00 seconds

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Best guess is that you are not entering a Time. The formula requires both a Date & Time to be entered like:

Start Date 10/11/2012 07:35:40 AM

End Date 07/23/2014 09:30:00 AMShould give you:

1 years, 9 months, 12 days, 01 hours, 54 minutes, and 20 seconds

MIKE

Here is the formula update so that C3 = A Start Date like: 10/11/2012 07:35:40 AM

and

C4 = A End Date like: 07/23/2014 09:30:00 AM=IF(C4="",DATEDIF(C3,NOW()-(MOD(C3,1)>MOD(NOW(),1)),"y")&" years, "&DATEDIF(C3,NOW()-(MOD(C3,1)>MOD(NOW(),1)),"ym")&" months, "&DATEDIF(C3,NOW()-(MOD(C3,1)>MOD(NOW(),1)),"md")&" days, "&TEXT(MOD(NOW()-C3,1),"hh "" hours, "" mm "" minutes, and "" ss ""seconds"""),DATEDIF(C3,C4-(MOD(C3,1)>MOD(C4,1)),"y")&" years, "&DATEDIF(C3,C4-(MOD(C3,1)>MOD(C4,1)),"ym")&" months, "&DATEDIF(C3,C4-(MOD(C3,1)>MOD(C4,1)),"md")&" days, "&TEXT(MOD(C4-C3,1),"hh "" hours, "" mm "" minutes, and "" ss ""seconds"""))

When you enter the formula in A5,

with the above Start & End Dates & Times you should get:1 years, 9 months, 12 days, 01 hours, 54 minutes, and 20 seconds

It is a very long formula, so I would suggest you Copy the formula from here and then Paste into your spreadsheet.

MIKE

Mike,

I tried your original formula and it worked perfectly.However when I entered the formula with changed cells it rendered the

00hr,00min,00sec.It's for a sobriety count up.23-Feb-85 to =TODAY().I can't seem to attach a screen capture.

I've imposed on your generosity with much appreciation.

Adage

23-Feb-85 to =TODAY()There is no need to use the =TODAY() function with the formula,

if you leave theEnd Dateemptyit will automatically use

Today's date to calculate the length of time.So with 02/23/1985 and NO time, just the date, in cell C3

and cell C4 left blank you should get:29 years, 5 months, 0 days, 12 hours, 46 minutes, and 55 seconds

The hours and minutes and second are calculated using 00:00:00 or

Midnight as the default time.The

End Dateis used toLock Inthe length of time, so no further

calculations occur.EDIT ADDED:

If you enter

Both Dateswith NO TIME you get 00:00:00 because you are subtracting midnight from midnight which equals zero.

MIKE

message edited by mmcconaghy

That works. I appreciate all the time and effort you've generously given.

Thank you.Adage

If the TIME part of the formula is what is giving you problems

and all you need is Year, Month, Days

then try this shortened version:=IF(C4="",DATEDIF(C3,NOW()-(MOD(C3,1)>MOD(NOW(),1)),"y")&" years, "&DATEDIF(C3,NOW()-(MOD(C3,1)>MOD(NOW(),1)),"ym")&" months, "&DATEDIF(C3,NOW()-(MOD(C3,1)>MOD(NOW(),1)),"md")&" days, ",DATEDIF(C3,C4-(MOD(C3,1)>MOD(C4,1)),"y")&" years, "&DATEDIF(C3,C4-(MOD(C3,1)>MOD(C4,1)),"ym")&" months, "&DATEDIF(C3,C4-(MOD(C3,1)>MOD(C4,1)),"md")&" days, ")

MIKE

That's solved. Gratfully-THANK YOU.

Adage

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History