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]

Yes we are trying to fix the issue above (below)
The way it looks above is exactly how it should function. However, depending on the date, it will not function properly. The main issues are rows 10 and 14
Such as entering the birthday 1989 12 1, on today's day you will see the month will become 24 when it should stay 12, making the sum 0. That will drop down to the following cell j14 and remain the same unless k15 is more than k14 making it 12 borrowing 12 months (1 year) from I14. I14 seems to remain the same when it should be 1 less.

The years worked are subtracted from row 14, providing the computed age answer.

My formulas in rows 10 and 14 are not able to determine when to add numbers when they are supposed to and minus numbers from the next cell over like it suppose to.

Try plugging in your dates and how many years you have been with the company. It should spit out how old you were when you started.

It's more of how old you would be minus the years you worked. So if you worked for 10 years and started at the age of 20 but are now, you're 30, this would show the calculation for that. To put it simply. I just get it down to the day.

 

[Alex Blakenburg]

I can probably figure out how to make your method work but it is a rather unusual approach.

Is there any reason you can't use the below ?
Copy it into G9 on a copy of your workbook (or a copy of the sheet will work)
I only needs rows 12 & 16 since it gets everything else from the input cells in the top left.
Note: It is not using the Years employed line but is calculating it from the Hire Date.
I have also not done the +1 we can add that if you think you need to.

20221202 Dates QACHECK-1 Newbienew.xlsx
	G	H	I	J	K	L	M	N	O
9			COMPUTED AGE
10	TODAY'S DATE		2022	12	2
11	BIRTHDAY		1989	12	1
12	AGE		33	0	1
13			↓	↓	2	<PLUS 1 FOR END DATE
14	SUM TOTAL			-1	32
15	MINUS YEARS EMPLOYED		11	0	13
16	COMPUTED AGE @ EMP DATE		21	5	0
Quality Assurance Check Date Di

Cell Formulas
Range	Formula
I10	I10	=IF(OR(D3<D2,AND(D3=D2,E3<E2)),C2,C2-1)
J10	J10	=IF(OR(D3>D2,AND(D3=D2,E3>E2)),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	=DATEDIF(DATE($C$3,$D$3,$E$3),DATE($C$2,$D$2,$E$2),"m")-I12*12
K10	K10	=IF(E2<E3,E2+DAY(EOMONTH(DATE(I10,J10,1),0)),E2)
K12	K12	=DATE($C$2,$D$2,$E$2)-EDATE(DATE($C$3,$D$3,$E$3),I12*12+J12)
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	=DATEDIF(DATE($C$3,$D$3,$E$3),DATE($C$4,$D$4,$E$4),"y")
J16	J16	=DATEDIF(DATE($C$3,$D$3,$E$3),DATE($C$4,$D$4,$E$4),"m")-I16*12
K16	K16	=DATE($C$4,$D$4,$E$4)-EDATE(DATE($C$3,$D$3,$E$3),I16*12+J16)

 

[Newbienew]

I see what you're getting at, but that puts the member at the age of 17, and it leaves the other cells blank or add up incorrectly. The date hired is only used for the number of years employed. Which all in all is not needed for this sheet as those numbers are on the document they turn in.

i have attached the link with your formulas attached. Hopefully I didn't place it wrong

 

[Alex Blakenburg]

I see what you're getting at, but that puts the member at the age of 17, and it leaves the other cells blank or add up incorrectly. The date hired is only used for the number of years employed. Which all in all is not needed for this sheet as those numbers are on the document they turn in.

i have attached the link with your formulas attached. Hopefully I didn't place it wrong

I can see no link. To send a sample spreadsheet you need to put it on a shared server such as Google drive, drop box, one drive etc make it available to anyone with the link and post the link here using the chain link button.

As it stands I have no visibility of when you are getting 17 and what you think it should be.

You need the hire date to calculate the years employed and it is more likely to be entered correctly than a user calculated years employed. Most people want to and it would be generally accepted as preferable to automate as much of the process as possible minimising the possibility of user error.

 

[Newbienew]

Sorry, thought I added it

QA Check

The years employed is not a calculation as that is on the documentation given to us. Everything in the left hand corner is provided.

 

[Alex Blakenburg]

Give this a try (copy into G9)

20221202 Dates QACHECK-2 Newbienew 20221208.xlsx
	G	H	I	J	K
9			COMPUTED AGE
10	TODAY'S DATE		2021	23	32
11	BIRTHDAY		1989	12	4
12	SUM TOTAL		32	11	28
13			↓	↓	29
14	SUM TOTAL		32	11	29
15	MINUS YEARS EMPLOYED		11	0	13
16	COMPUTED AGE		21	11	15
Quality Assurance Check Sheet

Cell Formulas
Range	Formula
I10	I10	=IF(OR(D3<D2,AND(D3=D2,E3<E2)),C2,C2-1)
J10	J10	=IF(OR(D3>D2,AND(D3=D2,E3>E2)),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	=DATEDIF(DATE($C$3,$D$3,$E$3),DATE($C$2,$D$2,$E$2),"m")-I12*12
K10	K10	=IF(E2<E3,E2+DAY(EOMONTH(DATE(I10,J10,1),0)),E2)
K12	K12	=DATE($C$2,$D$2,$E$2)-EDATE(DATE($C$3,$D$3,$E$3),I12*12+J12)
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	=DATEDIF( DATE( $C$3, $D$3, $E$3 ), DATE( $C$2 - $I$15, $D$2 - $J$15, $E$2 - $K$15 ), "y")
J16	J16	=DATEDIF( DATE( $C$3, $D$3, $E$3 ), DATE( $C$2 - $I$15, $D$2 - $J$15, $E$2 - $K$15 ), "m") - (I16*12)
K16	K16	=DATE( $C$2-I15, $D$2-J15, $E$2-K15 ) - EDATE( DATE( $C$3, $D$3, $E$3 ), I16 * 12 + J16 )

 

[Newbienew]

I tested it out and typed in all the numbers for any issues. Any days below for the birthday three leave I14 and J14 blank. If the day is 2 Cell I10 goes down 1 year, I14 remains blank, and j14 becomes -1.

k15 and k16 do not add up to k14 but equal k12.

 

[Alex Blakenburg]

I am afraid that unless you can show me when Rows 12 and 16 are returning incorrect results I am going to have to leave it there.

Your "helper" row 14 which is what you are currently referring to is not actually very useful. It seems to be trying to get the accuracy down to 1 day and by relying on +30 that is only going to happen in 5 months of the year. To get the formula on row 14 right you would need to replicate the formulas I have in row 16 and if that is the case you may as well just use Rows 16 & 12 to calculate it.

 

[Newbienew]

I am afraid that unless you can show me when Rows 12 and 16 are returning incorrect results I am going to have to leave it there.

Your "helper" row 14 which is what you are currently referring to is not actually very useful. It seems to be trying to get the accuracy down to 1 day and by relying on +30 that is only going to happen in 5 months of the year. To get the formula on row 14 right you would need to replicate the formulas I have in row 16 and if that is the case you may as well just use Rows 16 & 12 to calculate it.

Sorry for the late reply. I had a personal situation that needed my full attention. I do see your point, but this is more of a training/verification tool. So showing all the steps of how it works is needed. This is why the setup is the way it is. We have before used how long the member was with the organization minus their current age. This works for a few of our processing applicants but not all. We would be off by one year or two, and the paperwork would be kicked back for reprocessing, to which the handwritten method of what you see would be used.

 

[Alex Blakenburg]

I'm afraid I can help you fix rows 12 & 16 if you believe they are producing incorrect results but there is no way of getting the row 14 correct without it replicating most of the calculation from Row 16 or simply doing the maths from using rows 12,15,16. I don't see how that is going to be helpful from a training prespective.