Monthly headcount based on hire date formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1767492%22%20slang%3D%22en-US%22%3EMonthly%20headcount%20based%20on%20hire%20date%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1767492%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20looking%20to%20take%20an%20employee%20roster%20and%2C%20using%20a%20formula%2C%20put%20a%20%22Yes%22%20if%20the%20employee%20was%20active%20as%20of%20the%20respective%20month%20(based%20on%20comparing%20the%20hire%20date%20to%20the%20last%20day%20of%20the%20month%20in%20question)%20and%20%22no%22%20if%20they%20were%20hired%20after%20the%20month%20in%20question.%20Said%20differently%2C%20I%20am%20trying%20to%20create%20a%20monthly%20headcount%20based%20on%20hire%20date.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1767492%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1767520%22%20slang%3D%22en-US%22%3ERe%3A%20Monthly%20headcount%20based%20on%20hire%20date%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1767520%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F827443%22%20target%3D%22_blank%22%3E%40egspen2%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(%24D2%26lt%3B%3DE%241%2C%22yes%22%2C%22no%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20drag%20it%20to%20other%20cells.%20If%20I%20understood%20your%20logic%20correctly%2C%20please%20check%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1768681%22%20slang%3D%22en-US%22%3ERe%3A%20Monthly%20headcount%20based%20on%20hire%20date%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1768681%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThanks%20-%20this%20makes%20sense%20to%20me%20and%20is%20what%20I%20was%20trying%20to%20do.%20However%2C%20when%20I%20copy%20this%20same%20formula%20into%20my%20document%2C%20I%20get%20return%20different%20values%20(for%20example%2C%20I%20get%20a%20no%20when%20you%20get%20a%20yes).%20Did%20you%20have%20to%20re-format%20the%20date%20or%20anything%20with%20the%20source%20file%3F%20See%20attached%20for%20my%20sheet%20simply%20updated%20for%20the%20formula%20you%20suggested%20-%20not%20sure%20why%20we%20get%20different%20answers.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

I am looking to take an employee roster and, using a formula, put a "Yes" if the employee was active as of the respective month (based on comparing the hire date to the last day of the month in question) and "no" if they were hired after the month in question. Said differently, I am trying to create a monthly headcount based on hire date. 

3 Replies
Highlighted

@egspen2 

That could be

=IF($D2<=E$1,"yes","no")

and drag it to other cells. If I understood your logic correctly, please check attached file.

Highlighted

@Sergei Baklan Thanks - this makes sense to me and is what I was trying to do. However, when I copy this same formula into my document, I get return different values (for example, I get a no when you get a yes). Did you have to re-format the date or anything with the source file? See attached for my sheet simply updated for the formula you suggested - not sure why we get different answers.

Highlighted

@egspen2 

You keep information about the hire dates as texts, not as dates. At the same in columns headers somewhere are dates, somewhere are texts.

I'd suggest to keep all dates as dates (which are actually numbers in Excel), not as texts. For that first convert Hire Date with Text to Columns into the dates

image.png

After that be sure you have no texts in columns headers, change all texts on dates

image.png

With that it shall work