calc # of d, m, y, then round to nearest fraction
 

Hello, I have been reading a lot of the Q&A here, but I haven't found one
that worked perfectly for my spreadsheet. I even went to the Pearson site,
but I didn't have any luck with the formulas.

GOAL: Multi-function cell
Calculate the number of days, months, and years, then have each result
displayed as a specific number (rounded to the nearest fraction, preferably
to the 100th or even as a quarter fraction).

Can anyone provide the exact formula for me to use?

Please use the following cells:
A1 = Date Submitted
B1 = Date Approved
C1 = Length of Time: Days
D1 = Length of Time: Months
E1 = Length of Time: Years

Thank you very much!
--
Ruby

calc # of d, m, y, then round to nearest fraction
 

These formulas will give you the whole number of days, months, and years.

=DATEDIF(A1,B1,"MD")
=DATEDIF(A1,B1,"YM")
=DATEDIF(A1,B1,"Y")

Why would you want fractions? How much of a month is 1 day; 1/31 of a
month, or 1/30? If you really wanted to estimate the fraction portion you
could try these instead

=B1-A1
=(B1-A1)/30
=(B1-A1)/365

"Ruby" wrote:

Hello, I have been reading a lot of the Q&A here, but I haven't found one
that worked perfectly for my spreadsheet. I even went to the Pearson site,
but I didn't have any luck with the formulas.

GOAL: Multi-function cell
Calculate the number of days, months, and years, then have each result
displayed as a specific number (rounded to the nearest fraction, preferably
to the 100th or even as a quarter fraction).

Can anyone provide the exact formula for me to use?

Please use the following cells:
A1 = Date Submitted
B1 = Date Approved
C1 = Length of Time: Days
D1 = Length of Time: Months
E1 = Length of Time: Years

Thank you very much!
--
Ruby

calc # of d, m, y, then round to nearest fraction
 

Thank you, Sloth. The person who I'm preparing this for wants the results as
accurate and precise as possible, so that's why I'm using fractions. (I know,
if it were up to me, I'd be fine with integers.)

Your second set of functions worked very well...
1-Feb-06 1-Apr-07 424.00 d 14.13 m 1.16 y
....but the # of days in a month aren'ts always 30. I read in one post to
use "30.4..." (something like that), but that doesn't seem accurrate - or is
it?

The first set did work for same years, but it did not round to nearest
fraction:
28-Nov-05 6-Dec-05 8.00 d 0.00 m 0.00 y

The first set did not work with different years:
1-Feb-06 1-Apr-07 0.00 d 2.00 m 1.00 y

Any additional suggestions is much appreciated.

--
Thank you,
Ruby


"Sloth" wrote:

These formulas will give you the whole number of days, months, and years.

=DATEDIF(A1,B1,"MD")
=DATEDIF(A1,B1,"YM")
=DATEDIF(A1,B1,"Y")

Why would you want fractions? How much of a month is 1 day; 1/31 of a
month, or 1/30? If you really wanted to estimate the fraction portion you
could try these instead

=B1-A1
=(B1-A1)/30
=(B1-A1)/365

"Ruby" wrote:

Hello, I have been reading a lot of the Q&A here, but I haven't found one
that worked perfectly for my spreadsheet. I even went to the Pearson site,
but I didn't have any luck with the formulas.

GOAL: Multi-function cell
Calculate the number of days, months, and years, then have each result
displayed as a specific number (rounded to the nearest fraction, preferably
to the 100th or even as a quarter fraction).

Can anyone provide the exact formula for me to use?

Please use the following cells:
A1 = Date Submitted
B1 = Date Approved
C1 = Length of Time: Days
D1 = Length of Time: Months
E1 = Length of Time: Years

Thank you very much!
--
Ruby