In Excel 2007, how can I calculate time elapsed in years with one decimal since date A and date B?
I have date A in cell F5 and date B in cell AG11, and both are customized formated as "ÅÅÅÅMMDD" (Swedish for "YYYYMMDD"). Then I want to calculate time elapsed in years with one decimal since date A and date B. For example, it might have elapsed "0.5 year" (20171231-20170630).
Then I need the result formated as "0.5 year" or "2.5 years" for example (if more than 1.999999 then "years"; otherwise "year").
Is this possible to achieve without VBA?
Then I need the result formated as "0.5 year" or "2.5 years" for example (if more than 1.999999 then "years"; otherwise "year").
Is this possible to achieve without VBA?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, that worked now.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I tried now to change the decimal separator from point to comma, but couldn't as a lot of other cell's decimal point also was changed to comma. Is there any way to do this for this cell only? Only this cell have comma as decimal separator?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I think it should work to change from dot to comma as decimal separator:
https://www.experts-exchange.com/questions/29039326/In-Excel-2007-how-do-I-change-for-only-one-cell-from-dot-to-comma-as-separator-for-decimal-value.html
https://www.experts-exchange.com/questions/29039326/In-Excel-2007-how-do-I-change-for-only-one-cell-from-dot-to-comma-as-separator-for-decimal-value.html
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This was a great solution Rob, thanks! I get the decimal separator displayed as a comma now.
ASKER
Thanks, worked excellent now.
ASKER
Bill, when I tried your formula Excel highlighted the "AG11" part in your formula.
Hain, for your last formula I get a "#NUM!" error.
I have custom formating in both F5 and G11 as "ÅÅÅÅMMDD" and enter for example "2017-10-21" and it gets displayed as "20171021".