Date Fomula

[gymwrecker]

I'm trying to come up with a formula to determine the number of years, months, and days (in this format) when subtracting from a specific value, i.e.

17 Years, 0 Months, 0 Days (this will always be the same and will not change)

For example, I need to deduct 15 Years, 5 Months, 25 Days from 17 Years, 0 Months, 0 Days, and need the result to be in the same format (2 Years, 7 Months, 5 Days)

Any help is greatly appreciated.

 

[Dave Patton]

Please explain how you calculate the result that you show.

A few examples posted with the forums tool named XL2BB would be helpful.

If you convert your example to dates, how may days would be the difference?

 

[felixstraube]

That's not going to be so simple. Since the time span "1 year, 1 month" for example can be very different depending on where this time span starts. If the year has 366 days or 365, how many days the month in that time span have.
You would need to tell us a little more about how you want to subtract these times and what the expected result should look like.
Where do these timespans you are subtracting come from?
Can you share some more examples?

 

[gymwrecker]

I'm basically converting Active Duty (Army) points (1 active-duty day equals one active-duty point) into dates:

i.e., 5,992 points is the equivalent of 16.4 years, or 16 Years, 5 Months, 2 Days (you get this by dividing 5,992/365).

So, for example, in cell B3 I have the following value: 5,992, and in cell B4 I have the following formula =INT(B3/365)&" Years, "&INT(MOD(B3,365)/30)&" Months, "&ROUND(MOD((MOD(B3,365)),30),0)&" Days", and the result displayed in B4 is 16 Years, 5 Months, 2 days (this is the format I need).

I'm looking for a formula that returns the difference in Years, Months, and Days by Subtracting the result in B4 from 17 Years, 0 Months, 0 Days (this is a standard value and cannot be changed).

I hope this helps!!!!

 

[gymwrecker]

That's not going to be so simple. Since the time span "1 year, 1 month" for example can be very different depending on where this time span starts. If the year has 366 days or 365, how many days the month in that time span have.
You would need to tell us a little more about how you want to subtract these times and what the expected result should look like.
Where do these timespans you are subtracting come from?
Can you share some more examples?

I can use 365 as a standard....

 

[Dave Patton]

Please try to answer the questions in #2 and #3.

What is your source information and what is the expected result.
Please post your examples with the forum's tool named XL2BB.

 

[felixstraube]

I'm basically converting Active Duty (Army) points (1 active-duty day equals one active-duty point) into dates:

i.e., 5,992 points is the equivalent of 16.4 years, or 16 Years, 5 Months, 2 Days (you get this by dividing 5,992/365).

So, for example, in cell B3 I have the following value: 5,992, and in cell B4 I have the following formula =INT(B3/365)&" Years, "&INT(MOD(B3,365)/30)&" Months, "&ROUND(MOD((MOD(B3,365)),30),0)&" Days", and the result displayed in B4 is 16 Years, 5 Months, 2 days (this is the format I need).

I'm looking for a formula that returns the difference in Years, Months, and Days by Subtracting the result in B4 from 17 Years, 0 Months, 0 Days (this is a standard value and cannot be changed).

I hope this helps!!!!

Why don't we subtract the points and after that we covert to years, month... ? Would that work? Because I guess that 17 years is the equivalent of some number of points in the end. (17*365 = 6205 for example?)

 

[felixstraube]

Would this work for example?:

Book3
	A	B	C
1
2
3	6205	5992	213
4	17 Years, 0 Months, 0 Days	16 Years, 5 Months, 2 Days	0 Years, 7 Months, 3 Days
Sheet1

Cell Formulas
Range	Formula
C3	C3	=A3-B3
A4:C4	A4	=INT(A3/365)&" Years, "&INT(MOD(A3,365)/30)&" Months, "&ROUND(MOD((MOD(A3,365)),30),0)&" Days"

 

[Dave Patton]

Dates and Time 2024.xlsm
	A	B	C	D	E
1				Days in	365.2425
2	Target Days	6,209	6,209	Days in month	30.4369
3	Points to date	5,992	4,000
4	Days	217	2,209
5	Years	0.59	6.05
6	Months	7	72
7
8
9	6209.25	5992	217.25	2209
10	17	16.41	0.59	6.047222222
11	17 Years, 0 Months, 0 Days	16 Years, 4 Months, 26 Days	0 Years, 7 Months, 4 Days	6 Years, 0 Months, 17 Days
12
5bb

Cell Formulas
Range	Formula
B2	B2	=17*E1
C2	C2	=B2
E2	E2	=365.2425/12
B4:C4	B4	=B2-B3
B5:C5	B5	=B4/$E$1
B6:C6	B6	=INT(DOLLARFR(B4/$E$2,12))
A9	A9	=17*365.25
C9	C9	=A9-B9
A10:D10	A10	=YEARFRAC(0,A9)
A11:D11	A11	=INT(A9/$E$1)&" Years, "&INT(MOD(A9,$E$1)/$E$2)&" Months, "&ROUNDDOWN(MOD((MOD(A9,$E$1)),$E$2),0)&" Days"