dslreports logo
site
 
    All Forums Hot Topics Gallery
spc

spacer




how-to block ads


Search Topic:
uniqs
432
share rss forum feed


Cat Scratch

join:2005-06-27
kudos:1

[Excel] Dates in a spreadsheet

I've got a spreadsheet that deals with dates and am trying to format a resulting cell to show a specific format. For instance;

In cell C3 there is a start date 1/27/1994
In cell D3 is =TODAY()
And in cell E3 is =(D3 - C3) which is 6637 days

How do I get the resulting cell (E3) to show something like "18 years 2 months 3 days". (Or maybe cell F3 working with the result in E3).

Is this even possible? I'm totally lost when it comes to Excel 2003. Thanks for reading.



psafux
Premium,VIP
join:2005-11-10
kudos:2

I don't believe Excel offers that specific time/date format.

If you were to divide E3 / 365 you'd come up with years. If it needs to be specific you may have to look into scripting something.



Kilroy
Premium,MVM
join:2002-11-21
Saint Paul, MN

1 recommendation

reply to Cat Scratch

From Here

=DATEDIF(A1,B1,"y")&" years "&DATEDIF(A1,B1,"ym")&" months "&DATEDIF(A1,B1,"md")&" days"

A1=First Date
B1=Today

Tested in Excel 2010, don't know if it will work in 2003.


zach3
Zach
Premium
join:2000-05-04
Saint Louis, MO

Yes it works in EXCEL 2003.

Thanks for the link Kilroy.



Cat Scratch

join:2005-06-27
kudos:1
reply to Kilroy

Wow, my Google-fu sucks. I've been looking and reading for hours trying to come up with that one.

Thanks Kilroy.



JohnInSJ
Premium
join:2003-09-22
Aptos, CA

said by Cat Scratch:

Wow, my Google-fu sucks. I've been looking and reading for hours trying to come up with that one.

Teach a man to fish:

»www.google.com/search?q=excel+da···ths+days

(google for excel date formula years months days)

Third link in results has it

Or, just save time and always go to »www.cpearson.com/excel/

That site is my go-to place for "oh crap, how do you do that in excel" answers
--
My place : »www.schettino.us


Cat Scratch

join:2005-06-27
kudos:1

said by JohnInSJ:

said by Cat Scratch:

Wow, my Google-fu sucks. I've been looking and reading for hours trying to come up with that one.

Teach a man to fish:

»www.google.com/search?q=excel+da···ths+days

(google for excel date formula years months days)

Third link in results has it

Or, just save time and always go to »www.cpearson.com/excel/

That site is my go-to place for "oh crap, how do you do that in excel" answers

Well you see, that exactly is my problem... 'how to formulate Google queries'.

Oh sure, I've been to www.googleguide.com and tried to absorb the info posted. But it doesn't seem sink in. Either I put too much into the query, or not enough. And don't let me get started on how easily distracted I get looking at all the other links that show up (time killers all).

And yes, that site is now bookmarked (at the top) with a couple others dealing with Excel. Thanks