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,SECONDSI altered the start date and end date toC3 & 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.htmlmessage edited by Adage

See More: Formula for real time calculator

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 C3and cell C4 left blank you should get:29 years, 5 months, 0 days, 12 hours, 46 minutes, and 55 secondsThe hours and minutes and second are calculated using 00:00:00 orMidnight as the default time.The End Date is used to Lock In the length of time, so no furthercalculations 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.MIKEmessage 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 secondsmessage 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 AMEnd Date 07/23/2014 09:30:00 AMShould give you:1 years, 9 months, 12 days, 01 hours, 54 minutes, and 20 secondsMIKEhttp://www.skeptic.com/

Report •

Related Solutions

#4
July 23, 2014 at 05:05:05
 Here is the formula update so thatC3 = A Start Date like: 10/11/2012 07:35:40 AMandC4 = 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 secondsIt is a very long formula, so I would suggest you Copy the formula from here and then Paste into your spreadsheet. MIKEhttp://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 the00hr,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
 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 C3and cell C4 left blank you should get:29 years, 5 months, 0 days, 12 hours, 46 minutes, and 55 secondsThe hours and minutes and second are calculated using 00:00:00 orMidnight as the default time.The End Date is used to Lock In the length of time, so no furthercalculations 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.MIKEmessage 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, Daysthen 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, ")MIKEhttp://www.skeptic.com/

Report •

#9
July 23, 2014 at 10:59:14