Excel Magic Trick 1512: Count Workers Employed 1 to 6 Years Based on Hire Date? 9 Examples

In this video see how to count how many employees have worked for the company between 1 and 6 years, based on a hire date. See 8 examples of different formulas and Conditional Formatting.
Topics:
1. (00:06) Introduction
2. (01:13) TODAY Function
3. (01:44) EDATE Function for Lower Limit for counting between lower date and upper date. EDATE for upper limit formula too.
4. (03:08) COUNTIFS to count between lower and upper dates. Learn about how the comparative operator in COUNTISF requires quotes. Formula Counts Between a Lower & Upper Limit.
5. (04:41) AND Function Helper Colum for logical TRUE / FALSE formula. Learn about how the comparative operators in Logical Formulas do NOT require quotes.
6. (06:35) COUNTIFS function with TRUE criteria. Count Number of TRUE values.
7. (06:57) SUMPRODUCT Function to add the number of TRUE values. Add Number of TRUE values.
8. (08:47) Conditional Formatting Formula to highlight the employee records (highlight row) where the employee has worked for company between one to six years.
9. (11:40) One Complete Mashed Up Formula that does not require intermediate cells with formulas. Learn a lot of how you can copy and paste formula elements from intermediate cells into one final formula – huge mega formula.
10. (13:46) Summary

[mazarata]

Very practical examples thank you. But my highlight in this video: the best explanation on how cell references impact where conditional formatting will be applied. That's the best and most practical explanation. Thank you Mike.

[johnborg]

Amazing!!! It was nice of you to do a formula today, although Power Query is fun too. Thanks Mike :) :)

[rjbush]

Love the SUMPRODUCT hack with double negative.

[MalinaC]

Thank you for a vide for HR :)! EXCELlent, as always!

[tulsidasjamnani]

Good Job Mike


Your formula using tricks are ....

So tricky..., 😆

[barttitulaerexcelbart]

Mike, perfect. No critical remarks this time ;). I do have an idea however: Would it be interesting to add a sheet "Formulas" and list all used formulas start in A1, A2 etc. Later you could make a report in power query to see which formulas belong to which video's. Maybe for the future? Or is this a typically VBA job that generates this list in your existing video's?

[sjn]

Cool! Never used EDATE before. And I can never remember which functions require >= in quotes and which don’t.

[mattschoular]

Thanks Mike....Very well explained as always....

[davidjj]

Thank you Mike. Very useful to me in my daily work

[entertainmentgalaxy]

Thanks for EDATE function... BTW sum function will work with Ctrl Shit and Enter key...

[ismailismaili]

this is amazing thank you so much Mr. Mike

[raheel]

Its awesome...good job Sir.... impressive solution...thanks

[kamranb]

Great tip Mike :-). I received despatch confirmation email for excelisfun mug yesterday, hoping to get it by 20th the latest, coffee with excelisfun ;)

[Luciano_mp]

Mais uma excelente dica, função interessante, obrigado Mike.

[alwarhi]

May I ask you question?

I have 3 culumms at Excel chart, How to stacked two l culumms together and put third one behalf them (which mean 2 bars on one chart)?

[vikasdsem]

Sir I like it, but how Syntex automatic place to right of the formula you enter, I want to know sir.

[kibong]

Hi Mike, could you help me with my formula that can return "urgent" if it meets the condition?
The problem is that I have a lead time for date and time.

[KamalKumar-flzb]

Hi Mike, could you please help us with all cube formulas of excel ? An early response from your end will be appreciated.