Solved Formula for real time calculator

July 22, 2014 at 17:17:09
Specs: Windows XP
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


See More: Formula for real time calculator

Report •

✔ Best Answer
July 23, 2014 at 09:47:32
23-Feb-85 to =TODAY()

There is no need to use the =TODAY() function with the formula,
if you leave the End Date empty it 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 Date is used to Lock In the length of time, so no further
calculations occur.

EDIT ADDED:

If you enter Both Dates with NO TIME you get 00:00:00 because you are subtracting midnight from midnight which equals zero.


MIKE

http://www.skeptic.com/

message edited by mmcconaghy



#1
July 22, 2014 at 17:35:31
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


Report •

#2
July 22, 2014 at 19:04:42
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


Report •

#3
July 23, 2014 at 04:29:55
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 AM

Should give you:

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

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
July 23, 2014 at 05:05:05
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

http://www.skeptic.com/


Report •

#5
July 23, 2014 at 08:59:43
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


Report •

#6
July 23, 2014 at 09:47:32
✔ Best Answer
23-Feb-85 to =TODAY()

There is no need to use the =TODAY() function with the formula,
if you leave the End Date empty it 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 Date is used to Lock In the length of time, so no further
calculations occur.

EDIT ADDED:

If you enter Both Dates with NO TIME you get 00:00:00 because you are subtracting midnight from midnight which equals zero.


MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#7
July 23, 2014 at 10:20:34
That works.

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

Adage


Report •

#8
July 23, 2014 at 10:33:41
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

http://www.skeptic.com/


Report •

#9
July 23, 2014 at 10:59:14
That's solved. Gratfully-THANK YOU.
Adage

Report •

Ask Question