dslreports logo
 
    All Forums Hot Topics Gallery
spc
Search similar:


uniqs
457

Cat Scratch
join:2005-06-27

Cat Scratch

Member

[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 Member
join:2005-11-10

psafux

Premium Member

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
MVM
join:2002-11-21
Saint Paul, MN

1 recommendation

Kilroy to Cat Scratch

MVM

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 Member
join:2000-05-04
Saint Louis, MO

zach3

Premium Member

Yes it works in EXCEL 2003.

Thanks for the link Kilroy.

Cat Scratch
join:2005-06-27

Cat Scratch to Kilroy

Member

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 Member
join:2003-09-22
Aptos, CA

JohnInSJ

Premium Member

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= ··· 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

Cat Scratch
join:2005-06-27

Cat Scratch

Member

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= ··· 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