Expressing Time as a number in Excel

[normm]

I have a simple problem :
In Excel in cell A2 in want to write a date say Mar 2015.
In Cell B2 I want to write another date say Dec 2010.
In Cell C2 I want to write a formula that will give the difference ( A2 - B2)
ie the difference in time in Years ie a number like 4.2.

I want the date in Cells A2 and B2 in a format that is easily recognised by the reader like Mar 2015, Dec 2010.

Can you help please.

 

[MintJulep]

March 2015 is not a date.

 

[IDS]

If you type in Mar-2015 and Dec-2010 it will enter the date number for the first of the month, and display as Mar-15 and Dec 10. You can find the number of days between the two dates by subtracting the two cells, and the number of years by dividing that number by 365.25.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[cowski]

Have you tried the DateDif function?

http://www.excel-easy.com/examples/datedif.html

 

[IRstuff]

You can do that right now, without any functions. Dates in Excel are already numberized as the number of days since Jan 1 1901, where the decimal fraction is the fraction of the day. Just type in what you've specified and subtract the two, resulting in 275 days, which, as IDS, can be divided by 365.24 to get the number years.

TTFN
faq731-376

Need help writing a question or understanding a reply? forum1529


Of course I can. I can do anything. I can do absolutely anything. I'm an expert!
There is a homework forum hosted by engineering.com:

http://www.engineering.com/AskForum/aff/32.aspx

 

[IRstuff]

oops, days since Jan 1, 1900, sort of. Jan 1, 1900 12:00 am has a datecode of 1.00

TTFN
faq731-376

Need help writing a question or understanding a reply? forum1529


Of course I can. I can do anything. I can do absolutely anything. I'm an expert!
There is a homework forum hosted by engineering.com:

http://www.engineering.com/AskForum/aff/32.aspx

 

[normm]

Thanks a lot folks.
Simple and to the point. Much appreciated.

 

[MorTamir]

HAve you tried =YEAR(B2)-YEAR(A2)?

 

[IDS]

Year() gives an integer (the year number), but he wants a decimal. Dividing the difference of the date numbers by 365.25 does what he wants.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[zelgar]

Or you can use the YEARFRAC function as well.

=YEARFRAC(A2,B2)

 

[IRstuff]

For the OP's, example, they would need to use YEARFRAC(A2,B2,1) or YEARFRAC(A2,B2,1). The default setting of YEARFRAC (opion 0) uses 30 day months, hence 360 day years. Option 1 uses actual length of intervening years, and is therefore slightly more accurate than using a default 365.24 days. Option 3 uses 365 day years.

Note that there is no option for 365.24 day years.

TTFN
faq731-376

Need help writing a question or understanding a reply? forum1529


Of course I can. I can do anything. I can do absolutely anything. I'm an expert!
There is a homework forum hosted by engineering.com:

http://www.engineering.com/AskForum/aff/32.aspx

 

[IDS]

Note that there is no option for 365.24 day years.

Or should that be 365.2425?

http://en.wikipedia.org/wiki/Leap_year

On the other hand, if the time period does not include an end of century (or if that end of century is divisible by 400), then 365.25 is more accurate.

But for the precision required by the OP, 365, or even 360 would do

I hadn't heard of (or had forgotten about) Yearfrac. It's one of those functions where typing in the function name requires more keystrokes than just doing the arithmetic. I think there should be a name for that.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[IRstuff]

Well, we don't really know what accuracy the OP wants, but a 360-day year 1.4% off from the option 1 answer, and the 365.24-day year gives the next best answer. One thing nice about YEARFRAC option 1 is that it's technically exact, since it's supposedly using actual days per year for intervening years. Obviously, for long intervals, it's probably easier to use 365.24


TTFN
faq731-376

Need help writing a question or understanding a reply? forum1529


Of course I can. I can do anything. I can do absolutely anything. I'm an expert!
There is a homework forum hosted by engineering.com:

http://www.engineering.com/AskForum/aff/32.aspx