Two Topics: 1 Table related question, the other formula assistance requested.

[HalpNeeded]

Hello,

In plain English I am trying to do the following:

1) The row 1 date cells are Category "Custom" mmm-yy
2) The Hire/Attrition Date cells are Category m/d/yyyy

I am trying to count the number of months a person has been with the company (this is historical data) based on their hire date, by month. My poor formula does this after digging all around trying to figure out how the heck this works. If there is a better/cleaner way I am all ears.

I am also trying to expand the formula to when an attrition date is entered, the formula will pick that up and provide an ending value (red cell) with the cells to the right being blank or "N/A" etc. indicating the person is no longer with the company.

Does anyone have a suggestion how this can be accomplished?



My question about the Table is a bit more confusing...if I convert these values to a table without changing any formulas, or doing anything else, the formula seems to break because, i assume, the table starts doing strange Category stuff for headers that I simply cannot change (I change the Category to mmm-yy, or Date, or Number, General, etc. but nothing changes and the values are still showing 19, 20, etc.) Please see the screenshot for what I am talking about. Only the 2021 months are wrong, and I have no idea why.

I would appreciate any insight or help from you experts.

 

[kevin9999]

Does this give you what you want?

help needed.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	#	name	Hire Date	Attrition Date	Oct-21	Nov-21	Dec-21	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22
2	1	John	3/1/2021		7	8	9	10	11	12	13	14	15	16	17	18	19
3	2	Mary	4/23/2018	3/27/2022						47
4
5
6	#	name	Hire Date	Attrition Date	Oct-21	Nov-21	Dec-21	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22
7	1	John	3/1/2021		7	8	9	10	11	12	13	14	15	16	17	18	19
8	2	Mary	4/23/2018	3/27/2022						47
9
Sheet1

Cell Formulas
Range	Formula
E2:Q3,E7:Q8	E2	=IF(AND($C2<>"",$D2=""),DATEDIF($C$2,E$1,"M"),IF(AND($C2<>"",$D2<>"",(MONTH($D2)&YEAR($D2)=MONTH(E$1)&YEAR(E$1))),DATEDIF($C2,$D2,"M"),""))

 

[HalpNeeded]

Hello Kevin9999,

Sorry, I was out of town I did not see your reply. It is super close for sure. I worked through it with your help of MONTH($D2)&YEAR($D2)=MONTH(E$1)&YEAR(E$1

What I came up with (and added a few more Ifs, to ensure blanks were dealt with, etc.) was this:

=IF($D2="","",IF($D2>F$1,"",IF(AND($E2<>"",$E2>F$1),DATEDIF($D2,F$1,"M"),IF(AND($E2<>"", MONTH($E2)&YEAR($E2)=MONTH(F$1)&YEAR(F$1))),DATEDIF($D2,F$1,"M"),IF($E2="",DATEDIF($D2,F$1,"M"),"")))))

Thank you for your assistance, it pushed me to the right solution!