We're creating automations for HR. This is an employee tracker. We have every employee name, the department they're in, the salary, the start date , and country. We're going to add a few automations. This is going to be some formulas and some conditional formatting . We're going to show the tenure. We're going to highlight everyone who's over three years in the company. Anyone who's over a certain salary, we're going to create a little table with countries and the counts of each of those countries. We're going to create a review date, basically one year since they got hired, and we're going to highlight all of the dates that are reviewing this week. So let's start. Let's create tenure. We're going to use a two date diff formulas. Let's start with one date diff, we're going to select the start date , and the end date is today. CRM Automations MORE Simple Task Management Automations in Google Sheets

This is a function that will always change today's date as it changes. We're going to choose the year as the unit, or Y with capital Y, and then we're going to add an ampersand and some text that says years, comma, let's add a space and a quotation ampersand. And we'll, we'll add another date diff. This is going to be the same, D2 comma today. However, we're going to use YM. This is year months, or rather just months after we take off all of the years, because we have the years first, so we don't need those. We just need however many extra months that we have. We'll end that and we'll put an ampersand and another quote and say months, end quote. And we have here the tenure. How Many Days Between Two Dates? How Many Days Until 4th of July

Two years, four years. Pretty cool stuff, right? Let's highlight everyone over three years in the company. We're going to select the start date . Go up to Format , Conditional Formatting . We're going to use a custom formula . Our custom formula is going to be using DateDiff as well. D1 comma today. Okay. We'll also use Y in quotations and say anything greater than or equal to three, let's highlight it. You can highlight it any color. Let's say it's this green, dark green. Click done. And now everyone over three years of the company is highlighted green. Next, let's highlight all the salaries over 5, 000. Let's select the salary category or column here, format , conditional formatting, format rules, custom formula. Big Year Updated with PILLAR Template 80 Years In 1 Spreadsheet Highlight If Larger than Other Column

equals C1 is greater than 5, 000. And let's put that in orange. We can say C2 here and C2 there. And there we go. We're looking at all the correct ones in the correct range . Click done. We've now highlighted all the salaries over 5, 000. Let's create a little country table . We can do this on any page. Let's create a new tab. We're going to do equals unique and go and select our country tab. Country column. We'll get all of the unique countries and next to it we'll do count if the range will be employees, C colon C. Our criterion, we'll just select this country here. Count How Many Subscribers In a Company by BetterSheets.co Highlight Duplicates of Two Columns

Actually we need the E column, not the C column. Let's change C to E. There we go. And copy this all the way down and we'll see this is the number of countries we have as representatives for employees. Let's create a review date. Everybody will get a review one year later, after they've been hired. So we'll, equals edate, select the start date as the start date , and enter 12 months. And now we have a date for everyone that's one year after their start date . Let's highlight all the review dates this week, even if they're a different year., every year on the anniversary of their start date , we're going to review them, make sure we're reviewing them. So, we'll select this format , conditional formatting. We'll select custom formula. How To Change Date Format Automatically Create Calendar Event When Form Submitted

Equals week. Nu of G one is equal to week nu of today. Any dates that are this particular week are gonna be highlighted, so let's highlight them purple. Those are the ones that we need to be reviewing this week. There we had a few. There's another one. Click done Using weak, numb. No matter what the year is, we know that it's within this week. Pretty cool. We've completed all of our automations here. Hope you've enjoyed it. Hope you make your Google Sheets better and subscribe here to Better Sheets on YouTube. Study Better With Google Sheets Google Sheets Interface Changes How To Highlight Any Date One Week In Future