Ever wanted to get a set of dates, but don’t want to manually type? Here are three ways to insert dates in Excel automatically.
Option1: Enter a start date and Drag down
This is the easiest option if you just want a handful of dates. Just type in your starting date in a cell. Click in the bottom right corner of cell and drag down to get the next consecutive dates automatically.
Here is a quick demo of how to do this:
Insert just weekdays only
While dragging the dates, you can ask Excel to just insert weekdays only. This is helpful for creating dates for a project tracker or planner or other kinds of spreadsheets. To do this, after dragging your dates, click on the “options” button that appears at the bottom and select “Fill Weekdays Only” option.
See this demo:
Fill Dates in Months
You can also fill dates by Months. For this, just type the first date, drag down and use the “options” button to select “Fill Months” option. See this demo:
Automatic PayDays (or any other dates really!)
We can use the “dragging” mechanism to fill any kind of arbitrary dates too. Say, you want to see all the Pay Days in 2025. Type in the very first Pay Day (first Wednesday of the calendar year for example) and then, in the cell underneath, write the formula =cellabove+14 (replace cellabove with the actual address of the cell). Then drag this new cell down as far as you want.
See this quick demo:
Option 2: Using the “Fill Series” Secret Menu
Excel also offers a fairly powerful and easy way to fill dates if you want to get a large series of dates for a work project or spreadsheet. This is called “Fill Series” menu. This menu is hidden (buried really) but super helpful. Here is the step by step process:
- Type the very first date of your series of dates in a cell.
- Select a range big enough for all your dates. Tip: If you have a large series of dates to fill, just select all the cells in the column until end.
- Press the keyboard shortcut sequence
ALT H FI Sor Go to File Ribbon > Fill > Fill Series button - Select “Date” and specify the “date unit” from the choices – Day, Weekday, Month or Year.
- Enter the “step value” and “stop value” (tip: Use Step Value of 7 to get days filled by week, 14 for fortnight)
- Click “OK” to see the magic. The dates are filled by Excel automatically.
See this quick GIF to understand the whole process.
Related: Learn about fill-series option in Excel
Option 3: Using SEQUENCE Formula
This one is for hardcore Excel fans and people who love to automate things. We can use the “new” SEQUENCE function of Excel to auto generate dates from any starting point until any end point. The formula is really easy to use and offers a ton of flexibility when it comes to building date trackers, project sheets or even financial models.
Let me share two simple yet powerful examples:
SEQUENCE Dates Example 1: All Dates in Year 2025
To generate all the dates in Year 2025, go to an empty cell and type the below formula.
= SEQUENCE(365,, DATE(2025,1,1))
Syntax of SEQUENCE to get dates automatically=SEQUENCE(number of days,,starting date, optional step value)
Tip: Use the step value of 14 to generate dates by fortnight.
Working Days only – SEQUENCE Example 2
Let’s say for an upcoming project you want to list all the working days only. But you don’t know when the project starts. So you want to keep the “starting date” flexible and generate next “n” working days.
Imagine the start date of the project in cell C4 and number of working days in C5.
We can use below SEQUENCE function to get all the working days in the project.
=WORKDAY.INTL(C4,SEQUENCE(C5))
Bonus Tip: How to Set “Custom” Weekend Types
We can use WORKDAY.INTL function to tell Excel when your weekend is. For example if your weekend is “Friday & Saturday”, you can use below syntax:=WORKDAY.INTL(C4,SEQUENCE(C5),7)
Here 7 stands for Friday & Saturday weekend.
Why I love SEQUENCE() approach
Of the 3 techniques outlined here, SEQUENCE() based approach is my favorite.
- Flexible: Many real-world scenarios where I need dates are dynamic. The starting date, end date, step value and what I need (days / weekdays / weeks / months) all change. Using SEQUENCE() I can create a robust yet flexible auto listing of dates for my workbooks.
- Can be linked to other formulas: As SEQUENCE generates a dynamic spill range, I can use # to access the range and build other scalable and flexible formulas. For example, if I want to calculate depreciation schedules for next “n” months, I can do so easily. When “n” changes, I don’t need to adjust anything as both my dates (from SEQUENCE) and depreciation calculations auto adjust.
- Fewer errors: Spillable formulas like SEQUENCE mean, there is only one formula that produce all the results. This avoids crazy errors like inconsistent formulas or hard-coded values.
- Faster: Dynamic formulas like SEQUENCE() are really fast and scale well even when I need to list dates for next century!
Related: Learn how to use SEQUENCE and other Dynamic Functions in Excel
Take Caution when using SEQUENCE()
- SEQUENCE function doesn’t format the dates. So you must format the cells after (or beforehand) to see the correct date format. Else Excel will list the dates as 45658, 45659 for 1-Jan-2025, 2-Jan-2025 etc.
- SPILL Errors: As SEQUENCE will dynamically fill the cells if your spreadsheet doesn’t have enough space for the SEQUENCE() to fill all dates, it will throw SPILL error.
- Can’t be used in Excel Tables: SEQUENCE and other dynamic functions don’t work inside Excel tables. Read this page for more information and possible fixes.
- Need Excel 365 or Excel on Web: To use SEQUENCE function, you need Excel 365 or Excel on the Web as this is a new functionality and not supported in older versions of Excel.
Working with Dates in Excel – More Tips & Tricks
Dates are integral part of any spreadsheet and data analysis scenario. Please refer to below pages and resources to learn more about important Date functions and tricks.
- Working With Date & Time values in Excel – A quick primer
- Date Calculation Tips (future / past dates and times made easy)
- Using Power Query to auto-generate Dates
- Free 2025 Calendar & Planner Template (uses SEQUENCE)
- Calculate difference between two dates
- Highlight Due Dates in Excel automatically
- 3 Powerful & Essential Date functions in Excel
27 Responses to “Sum of Values Between 2 Dates [Excel Formulas]”
I would apply a filter and use function subtotal, with option 9. This way you can see multiple views based on the filter.
hey Chandoo, the solutions you proposed are very efficient, but if I wanted to be fancy I would do it this way .. the references are as your example workbook.
=SUM(INDIRECT("C"&(MATCH(F5,B5:B95)+4)):INDIRECT("C"&(MATCH(F6,B5:B95)+4)))
I like things simple:
=SUMIF(B5:B95,">="&F5,C5:C95)-SUMIF(B5:B95,">"&F6,C5:C95)
use something like: =SUM(OFFSET(B1,0,0,DATEDIF(A1,D1,"d")))
and have D1 be the date that I want to sum to.
In Excel 2003 (and earlier) I'd use an array formula to calculate either with nested if statements (as shown here) or with AND.
{=SUM(IF(B5:B95>F5,IF(B5:B95<F6,C5:C95,0),0))}
Note that I truly made this for BETWEEN the dates, not including the dates
I turned the data set into a table named Dailies.
I named the two limits StartDate and EndDate.
And used an array formula:
{=SUM((Dailies[Date]>=StartDate)*(Dailies[Date]<=EndDate)*Dailies[Sales])}
If I would still be using the old Excel I would do it as follows:
SUMIF($B$5:$B$95,"<="&H6,$C$5:$C$95)-SUMIF($B$5:$B$95,"<"&H5,$C$5:$C$95)
Works as simple as it is.
Regards
=sum(index(c:c,match(startdate,c:c,1)+1):index(c:c,match(enddate,c:c,1))
=sum(index(c:c,match(startdate,b:b,1)+1):index(c:c,match(enddate,b:b,1))
Great examples and thanks to Chandoo. You have simplified my work.
Hi! great tips I have found in your page, have you seen this
http://runakay.blogspot.com/2011/10/searching-in-multiple-excel-tabs.html
[...] I'm not sure I understand your question fully, but have a look at this: Sum of Values Between 2 Dates [Excel Formulas] | Chandoo.org - Learn Microsoft Excel Online [...]
Thank you! Thank you! Thank you!
=SUMIF(A2:A11;">="&B13;B2:B11)-SUMIF(A2:A11;"<"&A11;B2:B11)
awesome... thank yoo Chandoo!
which is most efficient and fast, if all are efficient ?
Thank you for this formula, I've just spent ages trying to find something to work on my data, I knew it would be possible! Don't care if others think there are easier/other ways to do it, you explained it so I understood it and could apply it to what I was doing so I'm happy!
The above said example is awesome for calculating values between dates,
can you pls let know how to calculate sale values if we have 10 sales boys for
ex: 1,rama
2,krishna
3,ashwin
4,naga
5,suresh
how much rama sale value between 1/jan/2015 to 10/jun/15
how much krishna sale value between 10/jan/2015 to 15/july/2015
i think you understood can you pls let me know the formula for how to calculate the sale between diffrent sale man sale value from master data file
Thanks,
Nagaraju
Hi
I have a list of people's names in column A, I have a list of dates in column B which records the dates they have been off sick, in column C I have either 1 if it is a full sick day or 0.5 if it is a half day.
What I would like to do is to add up the number of dates a specific person has been off within two dates.
For example, I want to look at my list of names and to find Joe Bloggs (column A), then add up all his sick days (column C). The start date will be in cell E1 and the end date will be in F1.
If this possible using SUMIFS?
List of names are in range A2:A100
List of dates in B2:B100
List of sick days (either 0.5 or 1 in C2:C100
The start date is in cell E2
The end date is in cell F2
Your help would be greatly appreciated.
Yes, with the help of SUMIFS you can have the solution.
Note: you need have an extra col. D2 where you will input Name of the person.
=SUMIFS(C2:C100,A2:A100,D2,C2:C100,">="&E2,C2:C100,"<"&F2)
Col. A Col. B Col. C Col.D Col. E Col. F
Name Date Sales
ABC 28-Jun-11 1 MNO 28-Jun-11 25-Sep-11
XYZ 29-Jun-11 0.5
MNO 30-Jun-11 1
PQR 1-Jul-11 1
Typo ERROR / Correction in formula:
Yes, with the help of SUMIFS you can have the solution.
Note: you need have an extra col. D2 where you will input Name of the person.
=SUMIFS(C2:C100,A2:A100,D2,B2:B100,">="&E2,B2:B100,"<"&F2)
Hi
I have a list of people's names in column A, I have a list of dates in column B which records the dates they have been off sick, in column C I have either 1 if it is a full sick day or 0.5 if it is a half day.
What I would like to do is to add up the number of dates a specific person has been off within two dates.
For example, I want to look at my list of names and to find Joe Bloggs (column A), then add up all his sick days (column C). The start date will be in cell E1 and the end date will be in F1.
If this possible using SUMIFS?
List of names are in range A2:A100
List of dates in B2:B100
List of sick days (either 0.5 or 1 in C2:C100
The start date is in cell E2
The end date is in cell F2
Your help would be greatly appreciated.
Viv
@Viv
Can you please post the question in the Chandoo.org Forums
http://forum.chandoo.org/
Please attach a file so that a specific answer can be delivered.
Thanks for this - it solved the problem that I was having. However can someone please explain to me why the "" needs to be around >= and <= as well as why we need to add & in order for the formula to work? Thanks in advance!
This formula works perfectly as well. Any ideas?: =SUM(INDEX(C5:C95,MATCH(H5,B5:B95,1)):INDEX(C5:C95,MATCH(H6,B5:B95,1)))
ikkeman had posted the same thing.
I am trying to sum total a range of cells between date ranges ie column n has $ amounts column d has the transaction dates ie 1/3/2015 or 25/3/2015 or 25/4/2015 column b has the text saying drp or distribution - reinv
In another cell I am trying to sum or total (in column n) with the value of a range of different dates (column d) that contain different text (column b) ie cell n48 is 50, n65 is 85, n165 is 36
with the dates ie cell d48 is 1/3/2015, d65 is 25/3/2015 and d165 is 25/4/2015
with different text that says drp or distribution - reinv ie cell b48 is drp, b65 is distribution - reinv, b165 is drp
If I wanted to sum the amounts between 1/3/2015 to 31/3/2015 with drp then the total would be 50. Also if I wanted to sum the amounts between 1/4/2015 to 30/4/2015 with drp the sum total would be 36 If I wanted to sum the amounts between 1/3/2015 to 31/3/2015 with drp and distribution - reinv the sum would be 115
What would the formula be for these different questions
hope you can help, it has been driving me nuts and cant work it out