Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Fill Dates With Weekdays/workdays Only

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

How do I fill a column's cells with a year's worth of weekdays only?

View Answers     

Similar Excel Tutorials

Dates in Excel Explained
I'll explain how dates in Excel work and then point you to many useful tutorials on how to use dates and also time ...
Get the Number of Workdays Between Two Dates in Excel
How to calculate the total number of working days between two dates in Excel.  This allows you to exclude all weeke ...
Get a Date that is So Many Working Days Before or After a Date in Excel - WORKDAY
How to find a date that is so many days before or after a specified date, excluding weekends and any holidays. This ...
Vlookup on Dates and Times in Excel
Perform a lookup on dates and times in Excel: vlookup, hlookup, index/match, any kind of lookup. This tutorial show ...

Helpful Excel Macros

Highlight the Column of the Selected Cell
- This macro will highlight the column of the active cell. This will fill the column of the active cell with a solid back
Highlight the Row and Column of the Selected Cell
- This macro will highlight the row and column of the active cell. This will fill the column and row of the active cell w
Return the ISO Standards Start of the Year in Excel - First Monday of the Year - UDF
- Return the date of the first Monday of any year with this UDF ISO Year function in Excel. This is a really simple and e
Highlight the Row of the Selected Cell
- This macro will highlight the row and column of the active cell. This will fill the row of the active cell with a solid
Add a Drop Down Menu to a Cell in Excel - Data Validation Macro
- Add a drop down menu or list to a cell in Excel with this free Excel macro. This is a great little macro that allows yo

Similar Topics


Trying to create a column in excel with each date in twice and only weekdays included. What is the best way to do this? I can highlight the cells with the right click button and then hit 'Fill Weekdays' to set up as weekdays but how do I create the series in weekdays and have two entries for each days e.g

Feb 5
Feb 5
Feb 6
Feb 6
Feb 7
Feb 7
Feb 8
Feb 8
Feb 11
Feb 11



I'm not doing something right. I tried networkdays and workdays... still, no good.

I'm trying to calculate the difference between two dates and exclude the weekends.

A = 03/21/07
B = 05/15/07
C = ???? how many weekdays ????

How would I write this formula?

I want a code that would look at a date in cell a1 and
fill the next 30 Weekdays in column B.

I know this may sound simple but it's driving me Nuts trying to figure it out!

Any Help would be appreciated!

Hello all,
How do I auto fill a series of 2014 dates in the row below the weekdays? I have a row C5 that has a series of auto fill weekdays successfully for 2014. I can't seem to auto fill the weekdates in the row below. I have to make manual adjustments for each Monday, and for the correct month ends, etc.
Do you have a solution for me?
Thank you.

I would like to be able to auto fill weekdays (skipping Saturday & Sunday) in a month. For example, my spreadsheet would look like this if cell A1 contains12/01/09:



The DATE column needs to return the date for WORKDAYS listed...Monday through Friday only. The first workday in Dec 2009 is 12/1, the fifth workday is 12/7, and so on...


I am working on a payroll schedule that needs to list the 1st and 15th pay periods (ie: 01/15/10, 01/31/10). I am trying to find a quick way to auto fill the page with these dates for the entire year. I am also need it to only fill weekdays.

Example: May 15th is a Saturday so the actual fill date would be 05/14/10, the next pay date would be 05/31/10, the next 06/15/10, 06/30/10, 07/15/10, 07/30/10, and so on...and so on.

I have done this before, but it has been so many years ago that I cannot remember how I did it. Please help!!

can someone plz help me with this:

i want to include all the dates in a column from 6/10/2003 till 6/9/2008, but i only want to include weekdays. if i just type in 6/10/2003 in the first cell,a nd then drag down, i will get all the dates up until 6/9/2008. but does anyone know how to make this only return the weekdays? thanks!


I was wondering whether someone could give me a hand with this function please?

At the moment I have the following formula which looks at 2 dates and returns the amount of weekdays between them.


this works no probs

however i now need a formula that will look at specific date (eg 01/01/08) and return another date so many weekdays later

for example 20 weekdays from the 01/01/08 would be .. 28/01/08, so i would need it to return this date. Any ideas how i could amend the formula above to do this or another way round it?



I'm trying to put together a sickness calculator that works on a rolling year basis.

I need to count the number of days between two dates, but only count the weekdays (exclude weekends and bank holidays) and only those dates which fall within the last year. To explain:

Let's say that today's date is 4 February 2010.

Column A holds 'Date from'.
Column B holds 'Date to'.
Column C counts the number of working weekdays between the two dates using the Networkdays function. (It correctly excludes weekends and bank holidays.)
Column D should count the number of working weekdays between those the Col A and Col B dates, as long as the dates are within the past year.

It's column D that I need assistance with.


A5 contains Wed 14 Jan 09
B5 contains Sun 18 Jan 09
C5 calculates as 3 (which is correct)
D5 should calculate as 0 - because this range of dates is over a year ago.

A6 contains Thur 18 June 09
B6 contains Fri 19 June 09
C6 calculates as 2
D6 should calculate as 2 - because this range of dates is within the past year.

A7 contains Tue 3 Feb 09
B7 contains Fri 6 Feb 09
C7 calculates as 4
D7 should calculate as 3 - because the 4th, 5th & 6th Feb 09 are within the past year but the 3rd Feb 09 is not.


Thanks in advance.

hi there

I'm using Excel 2003.
I have typed a date into cell A1 and dragged it down the column. I wish for it to fill with only weekdays.

Previously, once I had completed the drag a little icon popped up and I could select weeydays,series, copy etc .. but this is not there now for some reason ,

Does anyone know a work around or how to get it back ?



I have been using the following function to count the number of weekdays in a given range( Example for Mondays):

= SUMPRODUCT( --(WEEKDAY( ROW( INDIRECT( DATE($B$5,$C$5,$D$5) & ":" & DATE($B$6,$C$6,$D$6) ) ) ) = 2 ) )

I now need to be able to exclude holidays from this result. I noticed the NET WORKDAYS function but I don't know how to get it to work to only count for weekdays like my sumproduct function does.

I would attach the file but don't see an attachment option.

Any suggestions are greatly appreciated.

I've managed to get my data in this form:
******** ******************** ************************************************************************> Microsoft Excel - sf_charts_PUBLIC.xls ___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout Q12 T12 W12 Z12 T13 W13 Z13 Q15 T15 W15 Z15 P16 Q16 R16 S16 T16 U16 V16 W16 X16 Y16 Z16 AA16 O17 P17 Q17 R17 S17 T17 U17 V17 W17 X17 Y17 Z17 AA17 O18 P18 Q18 R18 S18 T18 U18 V18 W18 X18 Y18 Z18 AA18 O19 P19 Q19 R19 S19 T19 U19 V19 W19 X19 Y19 Z19 AA19 O20 P20 Q20 R20 S20 T20 U20 V20 W20 X20 Y20 Z20 AA20 O21 P21 Q21 R21 S21 T21 U21 V21 W21 X21 Y21 Z21 AA21 O22 P22 Q22 R22 S22 T22 U22 V22 W22 X22 Y22 Z22 AA22 O23 P23 Q23 R23 S23 T23 U23 V23 W23 X23 Y23 Z23 AA23 O24 P24 Q24 R24 S24 T24 U24 V24 W24 X24 Y24 Z24 AA24 O25 P25 Q25 R25 S25 T25 U25 V25 W25 X25 Y25 Z25 AA25 O26 P26 Q26 R26 S26 T26 U26 V26 W26 X26 Y26 Z26 AA26 O27 P27 Q27 R27 S27 T27 U27 V27 W27 X27 Y27 Z27 AA27 O28 P28 Q28 R28 S28 T28 U28 V28 W28 X28 Y28 Z28 AA28 O29 P29 Q29 R29 S29 T29 U29 V29 W29 X29 Y29 Z29 AA29 =
O P Q R S T U V W X Y Z AA 12 * * 11 * * 11 * * 11 * * 11 * 13 * * 1 * * 2 * * 3 * * 4 * 14 * SOLD,* SEATS,* UNUSED,* * * * * * * * * * 15 * * Tour*A*Weekdays*11:15*AM* * * Tour*A*Weekdays*9:00*AM* * * Tour*A*Weekends*11:45*AM* * * Tour*A*Weekends*9:00*AM* * 16 * SOLD,*Tour*A*Weekdays*11:15*AM* SEATS,*Tour*A*Weekdays*11:15*AM* UNUSED,*Tour*A*Weekdays*11:15*AM* SOLD,*Tour*A*Weekdays*9:00*AM* SEATS,*Tour*A*Weekdays*9:00*AM* UNUSED,*Tour*A*Weekdays*9:00*AM* SOLD,*Tour*A*Weekends*11:45*AM* SEATS,*Tour*A*Weekends*11:45*AM* UNUSED,*Tour*A*Weekends*11:45*AM* SOLD,*Tour*A*Weekends*9:00*AM* SEATS,*Tour*A*Weekends*9:00*AM* UNUSED,*Tour*A*Weekends*9:00*AM* 17 1/1/2007 2 2 0 0 0 0 0 0 0 0 0 0 18 1/2/2007 19 37 18 8 37 29 0 0 0 0 0 0 19 1/3/2007 29 37 8 0 0 0 0 0 0 0 0 0 20 1/4/2007 12 37 25 0 0 0 0 0 0 0 0 0 21 1/5/2007 24 37 13 9 24

I have a sheet with alle days an date in one month

in column "A" the date (format: TTTT TT.MM.JJ)
in column "B" I have the worked hours

Below in the sheet I would to have to sums
each one for weekdays and weekends,

because my guys get more money if they work on a saturday or sunday.

I have been playing with the formulars
=sumif(weekdays(....., but cant geht the right thing

Have any of you got a good solution to this ?


Is there a formula that can be created which tells you how old an item is only including weekdays (excluding weekends)? In other words, can Excel distinguish between weekdays and weekends?

Example: Let's say I have an entry dated 10/1/08. Is there a way to figure out how many weekdays there are between that day and today? I currenty use a formula similar to: =($J$1-A17) where $J$1 would be the current date and A17 is the date of the entry being aged. This formula includes weekends. I would only like weekdays.

Thank you


I have the following data at

I'm trying to figure out the average daily revenue. Range L4:L34 would give the total growth. I just need to find a way to find the average based on the amount of weekdays occurred during this month. Not any future weekdays. For example, if L4:L34 was sum of $10,000 and there were 9 weekdays it should return $1,111.12. I figured this formula

Please Login or Register  to view this content.

to count weekdays in my I4:I34 range. I just need to figure out a way so it doesn't include future dates. Just ones which have already occurred in the current month.

I want to print consecutive weekdays and dates on multiple copies of the same worksheet is there any way to program this into a cell or preferably a header or footer

copy 1 "Monday May 1st"
copy 2 "Tuesday May 2nd"

and so on

Thank you in advance

I have three cells. In the first cell there is a date. In the second cell there is a different date (more recent). In the third cell I want to have the number of WEEKDAYS between the two dates. I don't want to count saturdays and sundays.

What code in the third cell will do this? Is it even possible?


On a formatted schedule with weekdays as column heads, I need to be able to change any one day's date on the schedule (which automatically changes the subsequent dates) and onl have weekdays appear.
this is my IF argument that would not recognize the day name as the argument,=IF(L4="Fri",L4+3,L4+1). The result should be Monday.

Hi, I have a form where I select the Start Date and the End Date and it calculates the weekdays for me. Now I also need to omit the Bank/Government Holidays between two dates; Hence in addition to only calculating weekdays, I also want to make sure I am not counting any holidays like Easter etc. The formula I am currently using to calculate the weekdays is :


Help is much appreciated.

I used the search function and wasn't able to find what I was looking for. Probably using the wrong keywords.

I am putting together a schedule for an expense report. It has rows for 2 weeks worth of days (Monday - Friday). Some people who use the expense report may start on different days of the week so for example a function such as:

A1 = 03/06/08 [user input date]
A2 =(A1)+1 [Friday]
A3 =(A2)+3 [Changed to +3 because the 10th would be the Monday]
A4 =(A3)+1 [Tuesday]

Is there a formula I can put in A2 and subsequent cells that will skip over the weekends and go to the following Monday? Currently the +3 will have to be manually adjusted depending on the start date in A1 and this won't work for me. (Holidays or any other day we are closed are of no concern. Only weekdays).


In my Spreadsheet i have got First Day of Absence and Last Day of Absense Dates. Next column is Amount of Weekdays Absent using these to days and the formula =NETWORKDAYS(H6,I6)

If someone is currently off sick the Last DAy is completed. how can I stop a crazy Minus number appearing in the Total amount of Weekdays Absent?

I'm trying to created a spreadsheet functionality that will help me count the number of weekdays that occur in a range of dates, e.g., between 10/16/06 and 10/24/06 how many Mondays are there, Tuesdays, Wednesdays, etc (for all seven days of the week. The only information I will have to work off of is a start date and an end date. In general a date range will not be greater than 60 days, and usually about 5 - 14 days.

Does anyone have any suggestions?


can someone plz help me with this:

I have two columns: Column A represents the date, and column B represents the rate corresponding to the date in column A.

There are some missing dates in Column A. What I want to do is include all the missing weekdays in Column A, and then just use Excel's interpolation to come up with a rate for that missing date.

The only way I can think of doing this is creating a new column that lists all the weekdays from the beginning date to the end date and then manually figuring out which dates are missing, entering in new rows for those dates and then interpolating. This will take very long.

Does anyone know of a faster way I could do this? Thanks!

Is there any way that you can enter a date, display it in days and have Excel
fill in the workdays (not weekends or holidays) from that point on?

Thank you in advance for any help you can provide.


Hi guys i'm Claudio from Portugal and i'm new in this messageboard.
I have the following doubt:
How do i insert days of the month in column A starting for example in 01/01/2011, only with weekdays?
1 01/01/2011
2 ..
3 ..
4 ..