Fixed Monthly Value Between Dates

Today, we’re tackling an interesting challenge: How to add a fixed value to the first day of each month between a start and end date. We’ll explore three approaches, ranging from detailed to concise, so you can choose the one that best suits your needs.

Video

Step-by-step Tutorial

The task is straightforward:

1. Enter a start date and end date.
2. Automatically calculate a fixed value (e.g., $100) for the first day of each month between these dates.

We’ll walk through three approaches:

1. Listing all dates between the start and end date.
2. Listing only the first day of each month.
3. Directly calculating the total without intermediate steps.

Exercise 1: Listing All Dates

In this approach, we’ll generate all dates within the range and apply the fixed value to the first day of each month.

Let’s begin by setting up a few cells where we can enter the start date, end date, and the fixed monthly amount:

The desired result is a column of all days between the start and end dates, inclusive, plus a column of amounts which will show the fixed amount for day 1 of each month and zero for all other dates. Basically, we want something like this:

We’ll begin by generating a list of all dates between the start and end dates, inclusive.

Generate a Sequence of Dates
Use the SEQUENCE function to create a list of all dates between the start and end date. If you haven’t used this function before, here is a brief explanation:

• Syntax: SEQUENCE(rows, [columns], [start], [step])
• rows: Number of rows in the sequence.
• columns: (Optional) Number of columns in the sequence (default is 1).
• start: (Optional) Starting number of the sequence (default is 1).
• step: (Optional) Increment for each number (default is 1).

So, applying this function to our data, we could use something like this:

=SEQUENCE(C6-C5+1,1,C5)

The formula =SEQUENCE(C6-C5+1,1,C5) generates a column of numbers based on the dates in C5 and C6:

• C6-C5+1:
  • This calculates the number of rows for the sequence by subtracting the date in C5 from the date in C6 and adding 1.
  • It ensures the sequence includes both the start (C5) and end (C6) values.
• 1:
  • This specifies that the sequence will have only one column.
• C5:
  • This sets the starting number of the sequence to the date in C5.

Here is a partial view of the results when we write the formula in B12:

With our first column complete, it is time to tackle the amount column.

Mark the First Day of Each Month

We want to write a formula that checks whether the day of the date in spill range starting at B12 is the 1st of the month. If so, it should return the fixed amount in C7, otherwise, 0.

We will use the following formula to do so:

=IF(DAY(B12#)=1,C7,0)

• DAY(B12#):
  • Extracts the day component of the dates in the B12# spilled array).
• DAY(B12#) = 1:
  • Compares the extracted day to 1 to determine if the date is the first day of the month.
  • Returns TRUE if the day is 1, otherwise FALSE.
• IF(DAY(B12#) = 1, C7, 0):
  • If the day of the date in B12# is 1, the formula returns the value in cell C7.
  • If it is not the first day of the month, the formula returns 0.

We write the formula in C12 and bam:

What we have at this point is a list of all dates and the fixed value for day 1 of each month. The final step is to compute the total.

Calculate the Total
We’ll sum the values using the SUM function. We write the following formula into C10:

=SUM(C12#)

And bam:

And this accomplishes our objective. But, what if we wanted the results to be more concise. Like, what if we didn’t want to display every date … perhaps we just want to display all of the day 1s within the range. Well, that takes us to our next exercise.

Exercise 2: Listing First Days Only

We’ll start by using the same strategy as before. One column of dates and another column of amounts.

We’ll begin by using the same formula that generated our list of days, which was this:

=SEQUENCE(C6-C5+1,1,C5)

But, instead of displaying all of the dates, will use the FILTER function to only display the day 1s. We can accomplish this with the following formula:

=FILTER(SEQUENCE(C6-C5+1,,C5), DAY(SEQUENCE(C6-C5+1,,C5))=1)

Here is a breakdown of the formula:

SEQUENCE(C6-C5+1,,C5)

• This creates a sequence of dates starting from the date in cell C5 and ending with the date in C6.
• Note: since we omitted the optional columns argument, 1 column is the default.

DAY(SEQUENCE(C6-C5+1,,C5))

• This extracts the day number of the month for each date in the sequence generated by the SEQUENCE function.

DAY(SEQUENCE(...))=1

• This checks whether the day of the month is 1 for each date in the sequence.
• It produces an array of TRUE/FALSE values, where TRUE indicates that the date is the first day of a month.

FILTER(SEQUENCE(...), DAY(SEQUENCE(...))=1)

• The FILTER function takes the sequence of dates and filters it to include only those where the condition DAY(...) = 1 is TRUE.
• This results in a list of dates that are the first day of each month in the range.

We hit Enter, and bam:

With our list of dates complete, we just need to add the fixed monthly amount to each row. There are a variety of ways to accomplish this. One way that will dynamically spill when new date ranges are specified is this formula:

=SEQUENCE(B12#,,C7,0)

• B12#: This refers to the spilled range from cell B12. The size of this spilled range determines the number of rows in the sequence.
• ,,: Omitting the columns argument means the sequence will have only one column.
• C7: This specifies the starting value of the sequence, which comes from the value in cell C7.
• 0: This is the step value, meaning the sequence will increment by 0, so all values in the sequence will be equal to the starting value (C7).

We hit Enter, and bam:

If desired, we can also sum it up using the same formula as the first exercise.

But, what if instead of listing the months, we wanted it to be even more concise? Well, that leads us to our next exercise.

Exercise 3: Concise Total Calculation

The final approach leverages the same basic functions as the prior exercises. However, instead of displaying dates in cells, it simply displays the count of the number of day 1s in the range.

We begin with the same formula as the previous exercise:

=FILTER(SEQUENCE(C6-C5+1,,C5), DAY(SEQUENCE(C6-C5+1,,C5))=1)

Then, we simply wrap a COUNT function around it like this:

=COUNT(FILTER(SEQUENCE(C6-C5+1,,C5), DAY(SEQUENCE(C6-C5+1,,C5))=1))

We hit Enter, and bam:

For the total, we can simply multiply the resulting count by the fixed value in C7 with something like this:

=B11*C7

And the results:

And that is a very concise display of the total of the fixed monthly amount for each day 1 of the month within the date range … mission accomplished!

Conclusion

Each of these approaches build upon the same foundation of functions, but they offer flexibility for the desired level of detail to be displayed in the grid. Have questions or other Excel challenges? Share them in the comments below!

Sample File

Download a sample file with the working formulas.

FAQ

Q1. Can I use this method for other recurring dates, like the 15th of each month?

Absolutely! Replace the condition DAY(...) = 1 with DAY(...) = 15.

Q2. Will this work if my start and end dates don’t align with the first of the month?

Yes, the formulas automatically adjust. For example, a start date of 1/2/2030 will skip January.

Q3. Can I handle multiple fixed values (e.g., $50 in Jan, $100 in Feb)?

Yes, but you’ll need an additional lookup table with values assigned to each month.

Q4. What happens if the start date is after the end date?

The formulas return an error.