Sheets Functions – WEEKDAY, WORKDAY, NETWORKDAYS, EDATE, EOMONTH

Following on from my post on the basic date functions, let’s look at some really useful functions that work with dates, namely: WEEKDAY, WORKDAY, NETWORKDAYS, EDATE and EOMONTH, plus we’ll see an example with the CHOOSE function. With these we’ll:

• Find out the day of the week of a particular date
• Work out a deadline date
• Work out how many working days there are between two dates
• Easily set up start and end of the month dates
• Work out how many days there are in a month
• Work out how many working days there are in a month

---

Example 1 – What day of the week was a particular date?

What day of the week was 1st January 2000 on? No, I couldn’t remember either. Let’s use the WEEKDAY function to quickly find out.

In cell A1 I’ve written the date, then in cell B1 I write the following:

=WEEKDAY(A1)

This returns the day number for that date, where Sunday=1 and Saturday=7. So, we can see that 1st January 2000 was in fact a Saturday.

Personally, I find using Sunday as the first day of the week a bit confusing, but you can change which day is the first one by adapting the formula. Let’s make Monday the first day of the week. This time, after the date, add a comma then “2”. This makes Monday the first day.

=WEEKDAY(A1,2)

As we can see in cell B2, it now returns “6”, as Saturday is now the sixth day of the week.

You can make any day the first day, just by changing the number in the formula.

---

Example 2 – Returning the day of the week as text not as a number

The above example’s great, but it requires you to think of what the number represents. Wouldn’t it be better to return the actual name of the day? Well, that’s easily done by adding the CHOOSE function to our WEEKDAY one.

I write the following formula:

=CHOOSE(WEEKDAY(A1,2),“Monday”,“Tuesday”,“Wednesday”,“Thursday”,“Friday”,“Saturday”,“Sunday”)

This carries out the WEEKDAY function, finds the day number, then looks down the list of days. So, for the 1st Jan 2000, it will move along 6 spaces down the list and then choose the entry there, which of course is Saturday. Note, each entry needs to be in quotation marks.

The entries can be anything you want. For example, the other day I used this to return the days in Spanish despite using a sheet that was English-based, as we have both English and Spanish speakers using it.

A fun one to do in class with kids, is to find out what day of the week they were born.

---

Example 3 – Find out the date a number of days from a given date

In this example, a team have 90 working days to finish the project. I want to find out want date that is. To do so, I use the WORKDAY function:

=WORKDAY(B1,90)

This takes the start date in cell B1 and then adds 90 working days, and returns the end date. So, I can quickly see that they need to finish by 22nd March.

What about the Christmas holidays I hear you cry?! Well, WORKDAY can exclude a list of dates, such as holidays. In range D2 to D4, I’ve listed the Christmas and New Year holidays.

Back in our formula, I need to state where those holidays are, so I just add them after the “90”.

=WORKDAY(B1,90,D2:D4)

This time I see that the deadline’s moved out a little. Note, it’s only moved by one day, as out of 3 holidays, only one falls on a work day. In the UK, the weekend ones would in fact move to the Monday, but I just wanted to keep the example simple, and in other countries weekend public holidays don’t always move.

---

Example 4 – How many working days are there between two dates?

I’m looking forward to my Christmas break already and I want to know how more working days there are until I finish for Christmas. In cell B1 I put today’s date, in cell B2 the end of term date.

In cell B3, I write the following:

=NETWORKDAYS(B1,B2)

This takes the two dates and works out how many working days (Mon to Fri) there are. As we can see there are 26 days.

Ah, but in Spain we also have two public holidays on 6th and 8th December, I’m not working then.

We can exclude those from the total by modifying the formula:

=NETWORKDAYS(B1,B2,D2:D4)

This now works out the number of days and subtracts the number of days in range D2:D4. So, it turns out there are only 24 working days. Excellent!

---

Example 5 – Easily adding start of the month and end of the month dates

In the table below, I want to record how many students have had classes in each month. I need to include the start of the month and end of the month dates as I’m going to use them to filter a master list. Now, I could type in the dates, but with a long list this would be laborious. Instead I can use the EDATE and EOMONTH functions to do it for me.

In cell A2 I write the first date I want, in this case 1/10/2016. then in cell B2, I want to add the end of that month. I do this by writing the following:

=EOMONTH(A2,0)

This takes the date in cell A2 and then gets the date of the end of the month and as it’s the same month, I write a “0”. If I wanted the next month (i.e. 30/11), I would write “1” and so on.

On the next line I want to add the start of the next month (1/11). So, I write the following:

=EDATE(A2,1)

This takes the date in cell A2 and adds a month to it, keeping the same day of the month, i.e. 1st. In cell B2, I copy the same EOMONTH function as before, i.e. from cell B2.

Now for all future rows I can just copy this row and paste it below. So, for example, cell A6 is =EDATE(A5,1) and cell B6 is =EOMONTH(B5,0).

You can use EOMONTH to return the end of the month of future months, just by changing the 0 to a higher number. For example, in cell B2 =EOMONTH(A2, 3) would return 31/1/2017.

---

Example 6 – Working out the number of days in a month

In a salary sheet I need to know how many days there were in the month. Every month I type in the month in cell B1 and in cell B2 it tells me how many days are in that month, which I can then use in other formulas to calculate my teachers’ salaries.

In cell B2, I write the following:

=eomonth(B1,0)–B1+1

This gets the date in cell B1 and gets the end of month date, then subtracts the start of the month (B1) then adds one so it starts with one and not zero.

As you can see it rightly, worked out that in 2016 February had 29 days.

---

Example 7 – Working out the number of working days in a month

In the same salary sheet, I also need to know how many working days there were in that month. This time I need to combine the NETWORKDAYS function with the EOMONTH function. I write the following in cell B3:

=NETWORKDAYS(B1,EOMONTH(B1,0))

This gets the start date from cell B1, gets the end of the month date from the EOMONTH function and works out the number of working days in between.

So, it correctly worked out that there were 21 working days in February 2016.

---

There are two additional functions similar to WORKDAY and NETWORKDAYS, these are WORKDAY.INTL and NETWORKDAYS.INTL. These add the extra option of stating how many days of the week are non-working.

---