INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Expressing Time as a number in Excel

Expressing Time as a number in Excel

(OP)

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.

RE: Expressing Time as a number in Excel

March 2015 is not a date.

RE: Expressing Time as a number in Excel

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/

RE: Expressing Time as a number in Excel

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: Eng-Tips.com Forum Policies

Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers


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

RE: Expressing Time as a number in Excel

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

RE: Expressing Time as a number in Excel

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

RE: Expressing Time as a number in Excel

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/

RE: Expressing Time as a number in Excel

Or you can use the YEARFRAC function as well.

=YEARFRAC(A2,B2)

RE: Expressing Time as a number in Excel

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: Eng-Tips.com Forum Policies

Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers


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

RE: Expressing Time as a number in Excel

Quote:

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/

RE: Expressing Time as a number in Excel

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: Eng-Tips.com Forum Policies

Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers


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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close