Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

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

Years, Months, Days Between two dates in Excel
I have found a rather convoluted method to Calculate the Number of Years, Months and Days between to Dates in Excel ...
Sorting Data by Date, Text, or Number in Excel
Sorting Data can be done with a few quick clicks of the mouse. I have used the same data as the previous 2 tutorial ...
How to import Text Files (CSV) into Excel
Text files in CSV format are one of the easiest ways to store and transfer data as it is one of the most compatible ...
Excel Data Validation - Limit What a User Can Enter into a Cell
Data Validation is a tool in Excel that you can use to limit what a user can enter into a cell.  It is a great too ...

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...


XP Pro - Excel 2003 SP2

I maintain a monthly job task list and I prioritize jobs by how soon they need to be done.
Right now, I have a conditional format setup comparing the due date of the task to the current date. Depending on the amt of days, the row of cells containing all the information for that job has a different fill color. This is my current CF setup for Row 4 in this case:

Condition 1 =IF((B4-3)>=TODAY(),TRUE,FALSE) Format --- Fill Color "Light Yellow"
Condition 2 =IF((B4-2)>=TODAY(),TRUE,FALSE) Format --- Fill Color "Yellow"
Condition 3 =IF((B4-0)>=TODAY(),TRUE,FALSE) Format --- Fill Color "Gold"

My problem I just now noticed that on Fridays anything due on Monday is going to show as 3 days away and be a lower priority. So basically I need to compare Weekdays or Workdays instead of total days. I've attempted to use NETWORKDAYS but to no avail.

As a side issue: When I try to copy the format from left to right across the row, the cell I reference changes (as expected) and I have to manually change each one. Is there a way to make it carry over to any cell in the row I decided to Fill???

My apologies If my description wasn't clear enough.

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 want to create a sheet that lets the user enter the month/year in one cell and then another cell gives me the count of weekdays.

I'd be very happy with that. But, if someone knows how, to make it even snazzier, I'd like it instead to populate one cell with the number of weekdays and another cell to populate the number of Saturdays and holidays. Holidays come from a named range.

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 want to create a spreadsheet with the first column as the date. Normally I'd enter the starting date in the first cell, drag it down, click "Fill Weekdays", and I'd get a new date for each line. But what I want is the first 6 lines all the same date, the next 6 lines all the next date, the next 6 the next date, and so on ... weekdays only. How can I do this?


I am trying to do the following but I am not sure how to go about it. I want to create a form in which users can fill in their hours worked. I have 7 text boxes, 1 for each weekday, and two dropdowns in which i want to refer to week numbers and years.

First is the best way to fill this wk nr/year dropdown by refering to a seperate table with all the weekdays/years (I am a complete access newbie so probably not).

Second, how can i automatically populate the 7 text boxes with the dates of the selected wk/year?

Thanks for helping out this access newbie!



I need a formula to tell me how many weekdays between two dates, is this possible?



Good morning Board

Quick one for you guys:

Is there a way to obtain the workdays for last month? Excluding Holidays offcourse.

E.G. May only had 21 weekdays minus 1 holiday

total 20 workdays.

Thanx in Advanve.

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 ?


Does anyone know how to calculate the number of weekdays in a given month and year?

eg October 2006 = 22
December 2006 = 21 etc.

Many thanks,

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


Hello, I'd like to count make a regular count of weekdays from a set date in the form of a Min days to Max days away from that date.

presumably, I can start with

Day of Month (10)
Month of year(4)
Year ( 2009) { eg. 10th April 2009}

use the "Weekdays" Function to convert answer to Weekdays
Min days = +18
Max days = +24
draw a line from Min to Max

this could be as a Macro or VBA ( I have a little knowledge in them )
could someone assist please

thanks Keith

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?