Date Calculation Errors (YY|MM|DD)

[Newbienew]

Good Day all,

Attached is my spread of computing the number of years worked, a computed age calculator, and time away from work. The math is simple as it trickles down to the answers. If the bottom day is more than the top day, it's plus 30 days and the month is minus 1. If the bottom month is more than the top month, then it's plus 12 and the year is minus 1.

My issue is my math isn't mathing, so to say. My formulas are hit and miss, pending what is entered. I will at times get a negative result or be off by 1. My goal is to be absolute in my answers and I am not sure how to do that with the formula I have in each block. If someone could please aid me it would be greatly appreciated.

QA Workbook (click here)

 

[Newbienew]

I am soo sorry for the delay in reply. I have been extremely overtask at work and a couple of members of my team are pretty ill.
@Alex Blakenburg- I attempted to use the formula but it did not work out for me. The days calculated out to be 6884. I am not sure if this is due to the use of the date hired verse todays date. I want to make sure that each block of the formulas I have works the way they are supposed to get the outcome you have provided.

		COMPUTED AGE
TODAY'S DATE		0	0	0
BIRTHDAY		0	0	0
SUM TOTAL		25	4	9264
PLUS 1 FOR END DATE		↓	↓	9265
SUM TOTAL		0	0	0
MINUS YEARS EMPLOYED		0	0	0
		18	10	6884

I adjusted J14 formula to display the number 4 as it didnt before.

QACHECK.xlsx
	G	H	I	J	K	L
8			-1 Year	+12 months	+30 days
9			COMPUTED AGE
10	TODAY'S DATE		2021	16	42
11	BIRTHDAY		1996	12	30
12	SUM TOTAL		25	4	12
13	PLUS 1 FOR END DATE		↓	↓	13
14	SUM TOTAL		24	4	13
15	MINUS YEARS EMPLOYED		6	3	0
16			18	1	13
Quality Assurance Check Sheet

Cell Formulas
Range	Formula
I10	I10	=IF(D3<=D2,C2,C2-1)
J10	J10	=IF(D3>D2,D2+12,IF(D3<=D2,D2,D2-1))-IF(E2<E3,1,)
I11:K11	I11	=C3
I12,K12	I12	=I10-C3
J12	J12	=+J10-D3
K10	K10	=IF(E2<E3,E2+30,E2)
K13	K13	=K12+1
I14	I14	=IF(J14<J15,I12,I12-1)
J14	J14	=IF(J12<=D6,J12+12,J12)-IF(E6>K14,1,)
K14	K14	=IF(K15>K13,K13+30,K13)
I15:K15	I15	=C6
I16	I16	=(I14-C6)+IF(J16>30,1,)
J16	J16	=+IF(K16>30,J14-J15+1,J14-J15)
K16	K16	=IF(K14-K15>30,"0",K14-K15)

@Anthony47 - This section is all hand jammed in, except the today's date. All of this info feeds the other four sections. (Computed age, Time apart from work, years employed). However, I did place your formula in row 16 of C,D, and E and it works perfectly there.

2022	5	12
1996	12	30
2015	11	5
2022	2	4
6	3	0

Sorry for the double posting.

 

[Alex Blakenburg]

I attempted to use the formula but it did not work out for me. The days calculated out to be 6884. I am not sure if this is due to the use of the date hired verse todays date.

That would happen if you didn't increase the start date values in the formula like I had, to allow for what was already accounted for in the Year and Month columns.

In K12 I had:

=DATEDIF(DATE($C$3+$O12,$D$3+$P12,$E$3),DATE($C$2,$D$2,$E$2),"d")

And in K16 I had

=DATEDIF(DATE($C$3+$O16,$D$3+$P16,$E$3),DATE($C$4,$D$4,$E$4),"d")

 

[Newbienew]

I copied each one in to the each cell.

QACHECK.xlsx
	G	H	I	J	K	L
8			-1 Year	+12 months	+30 days
9			COMPUTED AGE
10	TODAY'S DATE		2021	16	44
11	BIRTHDAY		1996	12	30
12	SUM TOTAL		25	4	9266
13	PLUS 1 FOR END DATE		↓	↓	9267
14	SUM TOTAL		24	4	9267
15	MINUS YEARS EMPLOYED		6	3	0
16			18	10	6884
Quality Assurance Check Sheet

Cell Formulas
Range	Formula
I10	I10	=IF(D3<=D2,C2,C2-1)
J10	J10	=IF(D3>D2,D2+12,IF(D3<=D2,D2,D2-1))-IF(E2<E3,1,)
I11:K11	I11	=C3
I12	I12	=DATEDIF(DATE($C$3,$D$3,$E$3),DATE($C$2,$D$2,$E$2),"y")
J12	J12	=MOD(DATEDIF(DATE($C$3,$D$3,$E$3),DATE($C$2,$D$2,$E$2),"m"),12)
K10	K10	=IF(E2<E3,E2+30,E2)
K12	K12	=DATEDIF(DATE($C$3+$O12,$D$3+$P12,$E$3),DATE($C$2,$D$2,$E$2),"d")
K13	K13	=K12+1
I14	I14	=IF(J14<J15,I12,I12-1)
J14	J14	=IF(J12<=D6,J12+12,J12)-IF(E6>K14,1,)
K14	K14	=IF(K15>K13,K13+30,K13)
I15:K15	I15	=C6
I16	I16	=DATEDIF(DATE($C$3,$D$3,$E$3),DATE($C$4,$D$4,$E$4),"y")
J16	J16	=MOD(DATEDIF(DATE($C$3,$D$3,$E$3),DATE($C$4,$D$4,$E$4),"m"),12)
K16	K16	=DATEDIF(DATE($C$3+$O16,$D$3+$P16,$E$3),DATE($C$4,$D$4,$E$4),"d")

 

[Alex Blakenburg]

I had my table in a different position to what was in your spreadsheet.
Try this:

20220511 DateDif etc QACHECK v02 Newbienew.xlsx
	G	H	I	J	K
8			-1 Year	+12 months	+30 days
9			COMPUTED AGE
10	TODAY'S DATE		2021	16	45
11	BIRTHDAY		1996	12	30
12	SUM TOTAL		25	4	15
13	PLUS 1 FOR END DATE		↓	↓	16
14	SUM TOTAL		24	4	16
15	MINUS YEARS EMPLOYED		6	3	0
16			18	10	6
Sheet1

Cell Formulas
Range	Formula
I10	I10	=IF(D3<=D2,C2,C2-1)
J10	J10	=IF(D3>D2,D2+12,IF(D3<=D2,D2,D2-1))-IF(E2<E3,1,)
I11:K11	I11	=C3
I12	I12	=DATEDIF(DATE($C$3,$D$3,$E$3),DATE($C$2,$D$2,$E$2),"y")
J12	J12	=MOD(DATEDIF(DATE($C$3,$D$3,$E$3),DATE($C$2,$D$2,$E$2),"m"),12)
K10	K10	=IF(E2<E3,E2+30,E2)
K12	K12	=DATEDIF(DATE($C$3+$I12,$D$3+$J12,$E$3),DATE($C$2,$D$2,$E$2),"d")
K13	K13	=K12+1
I14	I14	=IF(J14<J15,I12,I12-1)
J14	J14	=IF(J12<=D6,J12+12,J12)-IF(E6>K14,1,)
K14	K14	=IF(K15>K13,K13+30,K13)
I15:K15	I15	=C6
I16	I16	=DATEDIF(DATE($C$3,$D$3,$E$3),DATE($C$4,$D$4,$E$4),"y")
J16	J16	=MOD(DATEDIF(DATE($C$3,$D$3,$E$3),DATE($C$4,$D$4,$E$4),"m"),12)
K16	K16	=DATEDIF(DATE($C$3+$I16,$D$3+$J16,$E$3),DATE($C$4,$D$4,$E$4),"d")

 

[Newbienew]

@Alex Blakenburg I attempted to dabble with your formula this week but I still can't figure out how to make it work with the days. I really wanted to message back with I got it but umm yea.

 

[Alex Blakenburg]

Did you copy the Xl2BB in my post and paste it to G8 in your spreadsheet ?

 

[Newbienew]

Did you copy the Xl2BB in my post and paste it to G8 in your spreadsheet ?

Thant I did good Sir

 

[Alex Blakenburg]

You did and it still didn't work or you just did and it worked ?

 

[Newbienew]

You did and it still didn't work or you just did and it worked ?

So I updated the link and added comments that I hope to explain better. In the posting above with your formula when you do it by hand the end result should be 19 1 16
My issue is each cell needs to feed the next cells based on the old stack math method.

Each step needs to be shown as I have people above my level that will still do it by hand or occasionally I will do it by hand as I did the one above.

Updated Sheet

 

[Alex Blakenburg]

It might be because it was so long ago but I am no really understanding what the we are trying to fix. Are we talking about this section below ?
What is not working and what should it show ?
What are you trying to calculate, it seems to be trying to calculate the age at the point of employment using the manually entered employment period on row 6 (which does not seem to be calculated using the hire date) ?

QACHECK-1.xlsx
	G	H	I	J	K
9			COMPUTED AGE
10	TODAY'S DATE		2022	11	32
11	BIRTHDAY		1989	9	18
12	SUM TOTAL		33	2	14
13			↓	↓	15
14	SUM TOTAL		33	2	15
15	MINUS YEARS EMPLOYED		11	0	13
16	COMPUTED AGE		22	2	2
Quality Assurance Check Sheet

Cell Formulas
Range	Formula
I10	I10	=IF(D3<=D2,C2,C2-1)
J10	J10	=IF(D3>=D2,D2+12,IF(D3<=D2,D2,D2-1))-IF(E2<E3,1,)
I11:K11	I11	=C3
I12,K12	I12	=I10-C3
J12	J12	=+J10-D3
K10	K10	=IF(E2<E3,E2+30,E2)
K13	K13	=K12+1
I14	I14	=IF(J12<J15,I12-1,IF(J14>=J15,I12,""))
J14	J14	=IF(K13<K15,J12-1,IF(J12<J15,J12+12,J12))
K14	K14	=IF(K15>K13,K13+30,K13)
I15:K15	I15	=C6
I16	I16	=(I14-C6)+IF(J16>30,1,)
J16	J16	=IF(K16>30,J14-J15+1,ABS(J14-J15))
K16	K16	=IF(K14-K15>30,"0",K14-K15)