Date Calculations

Ozu · 12/17/23

Hello All,

I'm working on creating a Holiday Dashboard, aiming to showcase the count of holidays taken per month, along with details about who took those holidays. Despite my attempts at formulating a solution, I haven't been able to achieve the desired results. I've attached a screenshot of what I've done so far for better understanding.

KPH · 12/19/23

PART 3

Now we can return to your other sheet to add some totals and names.

Step 9 Sum the columns

Use a cross sheet formula to sum the columns.

Repeat for the other months.

Step 10 Add the names

The best approach here could be to use a JOIN COLLECT - collecting the names from the name column where the Days in the month column are >0 .

=JOIN(COLLECT({Holiday Calendar Person}, {Holiday Calendar Feb 23}, >0), ", ")

Where the range Holiday Calendar Person is the Person column from your other sheet:

Repeat for other months, reusing the month range cross sheet references.

KPH · 12/18/23

It looks like Range 1 is a start date and Range 2 is an end date. And that you are creating a formula to look for any holiday that starts and ends in January and then returns the duration of the holiday. But you have an issue that it does not sum all the rows, and does not include holidays that start in one month and end in another.

Is that a reasonable description of what you are trying to do and the problems you are facing?

There is nothing in the formula regarding who took the holiday. How do you want that to appear?

Do you want to just count workdays?

Can holidays span one year, and into the next?

You are going to need a pretty lengthy formula, so it would be good to understand the requirements fully.

Ozu · 12/18/23

Hello @KPH,

Yes, you understood the problem well that's exactly what I am looking for.

There are two aspects to it. Firstly, I want to determine the number of holidays taken in January. The second part involves displaying the names of individuals who took those holidays. I haven't been successful in obtaining results for the first part, so I haven't attempted the second part yet.

KPH · 12/19/23

PART 1 of 3

Hi @Ozu

Unless someone has a better solution, I think you are going to need a lot of formulas to achieve what you want. Firstly, on the sheet in which the holidays are logged you will need to add columns to split the days into days per month. Then on your second sheet you can sum those columns.

Step 0 Add columns

Your first sheet should look something like this.

Step 1 Set up the dates

You might need to trust me on this until later in the formula, but I suggest using a sheet summary field to hold the first date of the month for each of the months you want to report on. This will make it easier to reuse the date in the formula and reduces the risk of you entering a date incorrectly and the effort involved in figuring out how many days there are in a month.

Set up sheet summary date fields and enter the 1st of each month into them (I am using US date format, so mine look like this)

Step 2 Formula to count number of days in given month if the holiday starts and ends within the month

This formula checks if the MONTH of the date in the Start Date column equals the MONTH of the date in the Feb 23 sheet summary. This is this bit in bold:

=IF(AND(MONTH([Start Date]@row) = MONTH([Feb 23]#), MONTH([End Date]@row) = MONTH([Feb 23]#)), DAY([End Date]@row) - DAY([Start Date]@row))

AND if the MONTH of the date in the End Date column equals the MONTH of the date in the Feb 23 sheet summary. This bit in bold:

=IF(AND(MONTH([Start Date]@row) = MONTH([Feb 23]#), MONTH([End Date]@row) = MONTH([Feb 23]#)), DAY([End Date]@row) - DAY([Start Date]@row))

If both things are true the value returned is the day from the End Date minus the Day from the Start Date. This bit in bold.

=IF(AND(MONTH([Start Date]@row) = MONTH([Feb 23]#), MONTH([End Date]@row) = MONTH([Feb 23]#)), DAY([End Date]@row) - DAY([Start Date]@row))

That is one of the formula you will need. It might not be quite right as I don't know if you want to only count work days or if you want to include the end date itself (i.e. 7-8th Feb is 2 is days), so the math at the end may been to be adapted once you know what you want. But this gives you a start that you can play with. You can put this in your February column. And copy it for March, April, May, etc. by changing these parts in bold to reference other start dates.

=IF(AND(MONTH([Start Date]@row) = MONTH([Feb 23]#), MONTH([End Date]@row) = MONTH([Feb 23]#)), DAY([End Date]@row) - DAY([Start Date]@row))

However, this will only count the days if the holiday starts and ends within the same month.

Step 3 Formula to count number of days in given month if the holiday starts before and ends within the month

This formula checks if the MONTH of the date in the Start Date column is before the MONTH of the date in the Feb 23 sheet summary. This is this bit in bold:

=IF(AND(MONTH([Start Date]@row) < MONTH([Feb 23]#), MONTH([End Date]@row) = MONTH([Feb 23]#)), DAY([End Date]@row))

AND if the MONTH of the date in the End Date column equals the MONTH of the date in the Feb 23 sheet summary. This bit in bold:

=IF(AND(MONTH([Start Date]@row) < MONTH([Feb 23]#), MONTH([End Date]@row) = MONTH([Feb 23]#)), DAY([End Date]@row))

In this situation the number of days in the month of interest is the day part of the end date. So the formula here is simple (unless we need to work with working days). The count of days is this part in bold:

=IF(AND(MONTH([Start Date]@row) < MONTH([Feb 23]#), MONTH([End Date]@row) = MONTH([Feb 23]#)), DAY([End Date]@row))

Step 4 Combine the two formula together

You have two IF functions and can nest them together by placing the second into the position to be evaluated if the first is not true.

So starting with this formula from Step 2.

=IF(AND(MONTH([Start Date]@row) = MONTH([Feb 23]#), MONTH([End Date]@row) = MONTH([Feb 23]#)), DAY([End Date]@row) - DAY([Start Date]@row))

Add a comma before the final closing parenthesis and add the formula from Step 3 (the changes are shown in bold).

=IF(AND(MONTH([Start Date]@row) = MONTH([Feb 23]#), MONTH([End Date]@row) = MONTH([Feb 23]#)), DAY([End Date]@row) - DAY([Start Date]@row) ,IF(AND(MONTH([Start Date]@row) < MONTH([Feb 23]#), MONTH([End Date]@row) = MONTH([Feb 23]#)), DAY([End Date]@row)))

This formula will now calculate the days in the given month if the start date is before or in the month and the end date is within the month.

But that is not all. You also need formula for if the end date is after the given month. You might be able to take it from here and complete the next two formula. Briefly the next steps are:

Step 5 Formula to count number of days in given month if the holiday starts before and ends after the month

Step 6 Formula to count number of days in given month if the holiday starts within and ends after the month

Step 7 Combine the formula created in steps 5 and 6 with the formula from step 4

Step 8 Copy/edit the formula to work on multiple columns (aka months)

Step 9 Sum the columns

Use a cross sheet formula to sum the columns.

Step 10 Add the names

The best approach here could be to use a JOIN COLLECT - collecting the names from the name column where the Days in the month column are >0 .

PART 2 follows....

KPH · 12/19/23

PART 2

Step 5 Formula to count number of days in given month if the holiday starts before and ends after the month

This formula checks if the MONTH of the date in the Start Date column is before the MONTH of the date in the Feb 23 sheet summary. This is this bit in bold:

=IF(AND(MONTH([Start Date]@row) < MONTH([Feb 23]#), MONTH([End Date]@row) > MONTH([Feb 23]#)), "days in the month")

AND if the MONTH of the date in the End Date column is after the MONTH of the date in the Feb 23 sheet summary. This bit in bold:

=IF(AND(MONTH([Start Date]@row) < MONTH([Feb 23]#), MONTH([End Date]@row) > MONTH([Feb 23]#)), "days in the month")

If both things are true the value returned is the text "days in the month".

=IF(AND(MONTH([Start Date]@row) < MONTH([Feb 23]#), MONTH([End Date]@row) > MONTH([Feb 23]#)), "days in the month")

You then need to replace the text "days in the month" (and the quotation marks) with another formula. This is a handy formula to calculate the number of days in a given month

=DATE(YEAR([Feb 23]#), MONTH([Feb 23]#) + 1, 1) - [Feb 23]#

It creates a date using the the year of the date specified in the sheet summary, month from the date specified in the sheet summary PLUS 1, and the day of 1. In other words, it finds the first date of the next month. You can then subtract the first date of the month of interest (which is the date in your sheet summary field) to get a count of the number of days in that month.

You could also replace this with a fixed value (like "28") but would need to remember to change this all for every month you use this for (and get the count correct!). I prefer to use the formula and limit the amount of changes from one column to the next. However, there is one complication. You cannot add a month to December. There is no month 13. So we need an additional IF.

=IF(MONTH([Feb 23]#) = 12, DATE(YEAR([Feb 23]#) + 1, 1, 1), DATE(YEAR([Feb 23]#), MONTH([Feb 23]#) + 1, 1)) - [Feb 23]#

This part in bold says that if the MONTH is month number 12 to return a date that is the year of the month in the sheet summary plus 1, month 1, day 1 (i.e. Jan 1st of the next year). Subtracting the 1st date of the month in the sheet summary from that (if the given month is December) gives you the 31 days in December.

Step 6 Formula to count number of days in given month if the holiday starts within and ends after the month

This formula checks if the MONTH of the date in the Start Date column is during the MONTH of the date in the Feb 23 sheet summary. This is this bit in bold:

=IF(AND(MONTH([Start Date]@row) = MONTH([Feb 23]#), MONTH([End Date]@row) > MONTH([Feb 23]#)), "TBC")

AND if the MONTH of the date in the End Date column is after the MONTH of the date in the Feb 23 sheet summary. This bit in bold:

=IF(AND(MONTH([Start Date]@row) < MONTH([Feb 23]#), MONTH([End Date]@row) > MONTH([Feb 23]#)), "TBC")

If both things are true the value returned is the text "TBC".

Here we can reuse part of the formula from step 5. Rather than subtracting the first of the month from the last day of the month to get the number of days in the month, we can subtract the start date. This gives us the number of days between the start date and the end of the month. This part in bold. Again, you might need to adjust the math here but you should be able to see how to do that.

=IF(MONTH([Feb 23]#) = 12, DATE(YEAR([Feb 23]#) + 1, 1, 1), DATE(YEAR([Feb 23]#), MONTH([Feb 23]#) + 1, 1)) - [Start Date]@row)

Combined with the logic above the full formula is

=IF(AND(MONTH([Start Date]@row) = MONTH([Feb 23]#), MONTH([End Date]@row) > MONTH([Feb 23]#)), IF(MONTH([Feb 23]#) = 12, DATE(YEAR([Feb 23]#) + 1, 1, 1), DATE(YEAR([Feb 23]#), MONTH([Feb 23]#) + 1, 1)) - [Start Date]@row)

Step 7 Combine the formula created in steps 5 and 6 with the formula from step 4

This is the formula from step 4

=IF(AND(MONTH([Start Date]@row) = MONTH([Feb 23]#), MONTH([End Date]@row) = MONTH([Feb 23]#)), DAY([End Date]@row) - DAY([Start Date]@row) ,IF(AND(MONTH([Start Date]@row) < MONTH([Feb 23]#), MONTH([End Date]@row) = MONTH([Feb 23]#)), DAY([End Date]@row)))

Here I have added the formula from step 5 (in bold)

=IF(AND(MONTH([Start Date]@row) = MONTH([Feb 23]#), MONTH([End Date]@row) = MONTH([Feb 23]#)), DAY([End Date]@row) - DAY([Start Date]@row), IF(AND(MONTH([Start Date]@row) < MONTH([Feb 23]#), MONTH([End Date]@row) = MONTH([Feb 23]#)), DAY([End Date]@row), IF(AND(MONTH([Start Date]@row) < MONTH([Feb 23]#), MONTH([End Date]@row) > MONTH([Feb 23]#)), IF(MONTH([Feb 23]#) = 12, DATE(YEAR([Feb 23]#) + 1, 1, 1), DATE(YEAR([Feb 23]#), MONTH([Feb 23]#) + 1, 1)) - [Feb 23]#)))

And here the formula from step 6 (in bold)

=IF(AND(MONTH([Start Date]@row) = MONTH([Feb 23]#), MONTH([End Date]@row) = MONTH([Feb 23]#)), DAY([End Date]@row) - DAY([Start Date]@row), IF(AND(MONTH([Start Date]@row) < MONTH([Feb 23]#), MONTH([End Date]@row) = MONTH([Feb 23]#)), DAY([End Date]@row), IF(AND(MONTH([Start Date]@row) < MONTH([Feb 23]#), MONTH([End Date]@row) > MONTH([Feb 23]#)), IF(MONTH([Feb 23]#) = 12, DATE(YEAR([Feb 23]#) + 1, 1, 1), DATE(YEAR([Feb 23]#), MONTH([Feb 23]#) + 1, 1)) - [Feb 23]#, IF(AND(MONTH([Start Date]@row) = MONTH([Feb 23]#), MONTH([End Date]@row) > MONTH([Feb 23]#)), IF(MONTH([Feb 23]#) = 12, DATE(YEAR([Feb 23]#) + 1, 1, 1), DATE(YEAR([Feb 23]#), MONTH([Feb 23]#) + 1, 1)) - [Start Date]@row))))

This is what my sheet looks like now.

Step 7.5 Add a 0 for holidays with 0 days in this month

If you wanted to add a 0 for any holidays that have 0 days in the month you could do this by returning 0 when all logic is false. To do this add ,0 before the first of the final set of closing parentheses.

=IF(AND(MONTH([Start Date]@row) = MONTH([Feb 23]#), MONTH([End Date]@row) = MONTH([Feb 23]#)), DAY([End Date]@row) - DAY([Start Date]@row), IF(AND(MONTH([Start Date]@row) < MONTH([Feb 23]#), MONTH([End Date]@row) = MONTH([Feb 23]#)), DAY([End Date]@row), IF(AND(MONTH([Start Date]@row) < MONTH([Feb 23]#), MONTH([End Date]@row) > MONTH([Feb 23]#)), IF(MONTH([Feb 23]#) = 12, DATE(YEAR([Feb 23]#) + 1, 1, 1), DATE(YEAR([Feb 23]#), MONTH([Feb 23]#) + 1, 1)) - [Feb 23]#, IF(AND(MONTH([Start Date]@row) = MONTH([Feb 23]#), MONTH([End Date]@row) > MONTH([Feb 23]#)), IF(MONTH([Feb 23]#) = 12, DATE(YEAR([Feb 23]#) + 1, 1, 1), DATE(YEAR([Feb 23]#), MONTH([Feb 23]#) + 1, 1)) - [Start Date]@row, 0))))

Step 8 Copy/edit the formula to work on multiple columns (aka months)

Add some $ to the formula to fix the Start Date and End Date references to make this easy to drag across multiple columns changing just the references to the sheet summary dates. This means that if you drag the formula to an adjacent column it will continue to use the Start Date and End Date columns and not the adjacent ones.

=IF(AND(MONTH($[Start Date]@row) = MONTH([Feb 23]#), MONTH($[End Date]@row) = MONTH([Feb 23]#)), DAY($[End Date]@row) - DAY($[Start Date]@row), IF(AND(MONTH($[Start Date]@row) < MONTH([Feb 23]#), MONTH($[End Date]@row) = MONTH([Feb 23]#)), DAY($[End Date]@row), IF(AND(MONTH($[Start Date]@row) < MONTH([Feb 23]#), MONTH($[End Date]@row) > MONTH([Feb 23]#)), IF(MONTH([Feb 23]#) = 12, DATE(YEAR([Feb 23]#) + 1, 1, 1), DATE(YEAR([Feb 23]#), MONTH([Feb 23]#) + 1, 1)) - [Feb 23]#, IF(AND(MONTH($[Start Date]@row) = MONTH([Feb 23]#), MONTH($[End Date]@row) > MONTH([Feb 23]#)), IF(MONTH([Feb 23]#) = 12, DATE(YEAR([Feb 23]#) + 1, 1, 1), DATE(YEAR([Feb 23]#), MONTH([Feb 23]#) + 1, 1)) - $[Start Date]@row, 0))))