## Fill Dates With Weekdays/workdays Only |

## Fill Dates With Weekdays/workdays Only - Excel |
View Answers |

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

TIA

TIA

## Free Excel Help Forum

**- Ask any question about Excel and have it answered in no time.**

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

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

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

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

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

- 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

- 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

- 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

- 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

- 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

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 ?

Thanks

Uli

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 ?

Thanks

Uli

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

Jeff

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

Jeff

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

copy 1 "Monday May 1st"

copy 2 "Tuesday May 2nd"

and so on

Thank you in advance

Hi.

First post here, so I salute everyone in this forum.

I'm not too savvy when it comes to Excel, but I've always been able to solve stuff (even complex stuff) just by googling a bit about it. This time is different and I've just had to register in this forum seeking help. I've even searched this forum for my issue but found nothing useful.

I'm a teacher in an high school. I have a number of groups which have classes at certain workdays but not in every workday. I have for example class x on mondays, tuesdays and thursdays, class y on tuesdays, wednesdays and freedays, and so on.

I need to log certain stuff for every class, and I want Excel to autofill me a list of dates when I have class x (or y, or z).

How can I tell Excel to show me a list of dates which include only certain workdays (say mondays, tuesdays and thursdays)? Is this possible?

TIA

PS: I'm working with Excel 2000, but I own also Office 2007, so any working solution will be OK.

First post here, so I salute everyone in this forum.

I'm not too savvy when it comes to Excel, but I've always been able to solve stuff (even complex stuff) just by googling a bit about it. This time is different and I've just had to register in this forum seeking help. I've even searched this forum for my issue but found nothing useful.

I'm a teacher in an high school. I have a number of groups which have classes at certain workdays but not in every workday. I have for example class x on mondays, tuesdays and thursdays, class y on tuesdays, wednesdays and freedays, and so on.

I need to log certain stuff for every class, and I want Excel to autofill me a list of dates when I have class x (or y, or z).

How can I tell Excel to show me a list of dates which include only certain workdays (say mondays, tuesdays and thursdays)? Is this possible?

TIA

PS: I'm working with Excel 2000, but I own also Office 2007, so any working solution will be OK.

Hello all,

I have a tricky problem. I am writing a rota for my work colleagues (doctors). I have generated a calculator that allocated the amount of on-call each colleague has to do - in terms of weekdays and weekends.

Each colleague is given a number of weekdays and weekends to work in a given time period (1 year).

My problem is how to put this into the rota in an automated way - allowing me to then change the allocated numbers, with the rota automatically updating itself. I'm sure this is possible, but I can't quite work out how to do it. The main problem I have is working out how to spread the shifts equally throughout the year.

I have attached a dummy rota (much simplified compared with the real thing). Sheet one is the number of shifts for each member of the rota. Sheet 2 is a blank rota which I need to populate.

Any help would be much appreciated!!

Many thanks,

Adrian

I have a tricky problem. I am writing a rota for my work colleagues (doctors). I have generated a calculator that allocated the amount of on-call each colleague has to do - in terms of weekdays and weekends.

Each colleague is given a number of weekdays and weekends to work in a given time period (1 year).

My problem is how to put this into the rota in an automated way - allowing me to then change the allocated numbers, with the rota automatically updating itself. I'm sure this is possible, but I can't quite work out how to do it. The main problem I have is working out how to spread the shifts equally throughout the year.

I have attached a dummy rota (much simplified compared with the real thing). Sheet one is the number of shifts for each member of the rota. Sheet 2 is a blank rota which I need to populate.

Any help would be much appreciated!!

Many thanks,

Adrian

I have two pieces of information. A begin date (A1) and a number of days (A2). I want to calculate the completion date. This is fairly simple

Code:

But how do I do this when I don't want weekends included?

Another way to say it is, A1 represents the number of weekdays, so I want to add that number of weekdays to A2 and see the final date.

So far I tried the WEEKDAY function, but did not get anywhere.

Thanks all!

NOTE: I am using EXCEL 2003

Code:

=A1+A2

But how do I do this when I don't want weekends included?

Another way to say it is, A1 represents the number of weekdays, so I want to add that number of weekdays to A2 and see the final date.

So far I tried the WEEKDAY function, but did not get anywhere.

Thanks all!

NOTE: I am using EXCEL 2003

Hi,

I am trying to find out how many specific weekdays exist in a month. For example, I need to know how many Mondays in a particular month.

I know how to get the working days in a month using the networkdays formula but is there any way then to link that to another formula which gives you a break down of the number of specific days e.g.

May has 22 work days, 5 Mondays, 5 Tuesdays, 4 Wednesdays, 4 Thursdays and 4 Fridays

Thanks

I am trying to find out how many specific weekdays exist in a month. For example, I need to know how many Mondays in a particular month.

I know how to get the working days in a month using the networkdays formula but is there any way then to link that to another formula which gives you a break down of the number of specific days e.g.

May has 22 work days, 5 Mondays, 5 Tuesdays, 4 Wednesdays, 4 Thursdays and 4 Fridays

Thanks

Hello everyone

After constantly searching for the answer I really need your help.

I'm busy with my final assignment for Management Decision Making, namely Project Scheduling.

We need to calculate the days necessary to complete the project, however it can only be the weekdays (as they want to give rest during the weekends to their employees). So I know the start date of the project (Mai 3) and the number of days every task need to be completed.

So if i put in column A 03/05/2009 and in column C 2 work days, then I want to calculate the date in column B. If I do just the normal excel calculations I get 05/05/2009 (by =Column A + column C).

But this is a continuos flow, so this does not take into account weekends (no labour).

So can somebody help me to fix this? It would be ideal that Excel calculates this and I don't need to do it by hand...

Any suggestions?

Many thanks

(ps. yes I checked EDATE and NETWORKDAYS -function, however this does not solve my problem.)

After constantly searching for the answer I really need your help.

I'm busy with my final assignment for Management Decision Making, namely Project Scheduling.

We need to calculate the days necessary to complete the project, however it can only be the weekdays (as they want to give rest during the weekends to their employees). So I know the start date of the project (Mai 3) and the number of days every task need to be completed.

So if i put in column A 03/05/2009 and in column C 2 work days, then I want to calculate the date in column B. If I do just the normal excel calculations I get 05/05/2009 (by =Column A + column C).

But this is a continuos flow, so this does not take into account weekends (no labour).

So can somebody help me to fix this? It would be ideal that Excel calculates this and I don't need to do it by hand...

Any suggestions?

Many thanks

(ps. yes I checked EDATE and NETWORKDAYS -function, however this does not solve my problem.)

Anyone know how to get the count of the number of days (weekdays and

saturdays) in a given month.

For example, if I were to forecast revenue using the logic:

Average revenue per day is x

My store will be open for y days in month z

I would want the number of days (weekdays and saturdays) to calculate

automatically for any given month...

Thanks for your input!

Bryan

saturdays) in a given month.

For example, if I were to forecast revenue using the logic:

Average revenue per day is x

My store will be open for y days in month z

I would want the number of days (weekdays and saturdays) to calculate

automatically for any given month...

Thanks for your input!

Bryan

Hello,

I like to add one gradient fill to a group of cells.

I have an area of cells that are 8x14 cells.

I used conditional formatting and created conditions for solid colors.

This looks good but I need to make it with gradient fill.

After choosing a two-color design it paints the individual cells instead of

creating one gradient fill for the groupped area of cells.

How can I do the conditional formatting with Gradient fill to cover the highligted cells as one gradient fill.

Please help,

Harry

I like to add one gradient fill to a group of cells.

I have an area of cells that are 8x14 cells.

I used conditional formatting and created conditions for solid colors.

This looks good but I need to make it with gradient fill.

After choosing a two-color design it paints the individual cells instead of

creating one gradient fill for the groupped area of cells.

How can I do the conditional formatting with Gradient fill to cover the highligted cells as one gradient fill.

Please help,

Harry

I want to shade cells in a section I have as a calendar based on dates in a column.

Row 1 E1:NE1 = each day in the year (vertically)

Column 1 = begin date

Column 2 = end date

If Cell C5 shows 1/14 and Cell D5 shows 2/28, I want the corresponding cells in row 5 under the dates from 1/14 through 2/28 to fill-in with shading, in this case Q5:BK5.

I will add many rows below it with other begin/end dates and will want their corresponding cells to be shaded.

Any suggestions?

Thanks,

~Beth

Row 1 E1:NE1 = each day in the year (vertically)

Column 1 = begin date

Column 2 = end date

If Cell C5 shows 1/14 and Cell D5 shows 2/28, I want the corresponding cells in row 5 under the dates from 1/14 through 2/28 to fill-in with shading, in this case Q5:BK5.

I will add many rows below it with other begin/end dates and will want their corresponding cells to be shaded.

Any suggestions?

Thanks,

~Beth

hi all,

I have a cell (A18) that continues to fill red once I type something in it seemingly without any instructions from me. There are other cells in the column which are filled red but not adjacent to this one (A1:A14 and A48:A49).

I have formatted the cell to have no fill and yet it happens again. I tried to drag the formatting from a cell below and it clears the fill but as soon as I type again it fills red!!!

What else can I do?

I have a cell (A18) that continues to fill red once I type something in it seemingly without any instructions from me. There are other cells in the column which are filled red but not adjacent to this one (A1:A14 and A48:A49).

I have formatted the cell to have no fill and yet it happens again. I tried to drag the formatting from a cell below and it clears the fill but as soon as I type again it fills red!!!

What else can I do?

Hi everyone,

this site is awesome. Thank you all for your input.... it is so helpful.

Something I can't seem to figure out, is how to enter data in non-adjacent cells using a fill command.

Here is what I am trying to do:

in the column, I am holding ctrl button to select every 10th cell down the sheet. I need to enter a date in every selected cell that is exactly 7 days apart, i.e., 11/7/08 then 11/14/08 then 11/21/08, etc.... but no other dates or data.

I have tried to figure out a way to do this other than manually, but am confounded.

Hope you can help me... my boss is waiting to see if I can pull it off.

Thanks!!!

this site is awesome. Thank you all for your input.... it is so helpful.

Something I can't seem to figure out, is how to enter data in non-adjacent cells using a fill command.

Here is what I am trying to do:

in the column, I am holding ctrl button to select every 10th cell down the sheet. I need to enter a date in every selected cell that is exactly 7 days apart, i.e., 11/7/08 then 11/14/08 then 11/21/08, etc.... but no other dates or data.

I have tried to figure out a way to do this other than manually, but am confounded.

Hope you can help me... my boss is waiting to see if I can pull it off.

Thanks!!!

Hey all,

I've been out of the loop for while again and I've got a little (lot) rusty ...

I want to change the 'FILL Pattern' format of a cell to 25% gray when <> NUL, and to 'NO Fill Pattern' when = NUL or zero... [The 25% specs/dots fill pattern superimposed on top of the background fill color, but not the fill color itself]...

The basic code I understand, what I need quick help with is the proceedure or code(s) to change specifically the Fill Pattern (NOT the fill color, just the pattern) and then undo the fill pattern (Again, not erase all formatting, just remove the 25% fill pattern, leaving the background fill color intact ...)

Any ideas ?

THANKS AS ALWAYS !

I've been out of the loop for while again and I've got a little (lot) rusty ...

I want to change the 'FILL Pattern' format of a cell to 25% gray when <> NUL, and to 'NO Fill Pattern' when = NUL or zero... [The 25% specs/dots fill pattern superimposed on top of the background fill color, but not the fill color itself]...

The basic code I understand, what I need quick help with is the proceedure or code(s) to change specifically the Fill Pattern (NOT the fill color, just the pattern) and then undo the fill pattern (Again, not erase all formatting, just remove the 25% fill pattern, leaving the background fill color intact ...)

Any ideas ?

THANKS AS ALWAYS !

heres what i want to do...

assign/fill cell b2 with a specific color

assign/fill cell d2 with a different color

assign/fill cell f2 with another

select a group of cells (b4-b7) and while selected, be able to then click cell b2 and the color of the selected cells will change to whatever color i have designated in cell b2.

i am trying to make a user friendly type of timeline spreadsheet where the user can just select a group of dates along the timeline and color them without having to look for the matching color in the color picker. i will have red, green, blue, and no fill, so if the dates change they can "erase" them with no fill.

in essance the colored cells would just be shortcut "buttons" they would not have any editable type or function to them

make sense? i just need to know the process, but a sample code (if necissary) would be appriciated

assign/fill cell b2 with a specific color

assign/fill cell d2 with a different color

assign/fill cell f2 with another

select a group of cells (b4-b7) and while selected, be able to then click cell b2 and the color of the selected cells will change to whatever color i have designated in cell b2.

i am trying to make a user friendly type of timeline spreadsheet where the user can just select a group of dates along the timeline and color them without having to look for the matching color in the color picker. i will have red, green, blue, and no fill, so if the dates change they can "erase" them with no fill.

in essance the colored cells would just be shortcut "buttons" they would not have any editable type or function to them

make sense? i just need to know the process, but a sample code (if necissary) would be appriciated

Hi guys,

could someone here can help me regarding the date/time function.

I have spreadsheet that i want automatic fill-out the column A when Column B is not empty. when i enter name in column B, column A will fill automatic with Dates and Time Today. i have this formula =IF(F3<>"",NOW(),"") works but everytime i open the spreadsheet on the next day all the dates were changed in todays date.

i want to come up as per example below.

i.e

Column A Column B

Date Started Name

07/28/2010 11:09AM Albert

07/29/2010 7:10PM Lexi

could someone here can help me regarding the date/time function.

I have spreadsheet that i want automatic fill-out the column A when Column B is not empty. when i enter name in column B, column A will fill automatic with Dates and Time Today. i have this formula =IF(F3<>"",NOW(),"") works but everytime i open the spreadsheet on the next day all the dates were changed in todays date.

i want to come up as per example below.

i.e

Column A Column B

Date Started Name

07/28/2010 11:09AM Albert

07/29/2010 7:10PM Lexi

I would like to fill several cells with a color fill and then apply the gradient option to the cells as a group. Excel 2003 will not allow the options for cell color fill. The options are available for a text box. Does anyone know how to color several cells and have the color fill subject to the gradient over the several cells?

Basically I have Titles in Row 1 and formulas in row 2. A:3 through Apick a number) has data in it. I then use the fill down function to fill the formulas from B:2 C:2 E:2 and so on. I generally fill down by double clicking the fill square and it automatically fills as far down as the column beside it. I then created a macro to do this for me, how do I get the macro to fill down only as far as the column beside it?

Code:

Here is what I have so far which would be fine if the amount of rows in column A never changed.

I tried adding the IF ISBLANK into every formula and then making my range to B:9000 or something but a couple of my formulas are kissing the character limit and I can't get around it. I know I could just make another cell somewhere with some values and use references but my sheet is already very large and I need to keep it clutter free

Code:

Range("B2").Select Selection.AutoFill Destination:=Range("B2:B18")

Here is what I have so far which would be fine if the amount of rows in column A never changed.

I tried adding the IF ISBLANK into every formula and then making my range to B:9000 or something but a couple of my formulas are kissing the character limit and I can't get around it. I know I could just make another cell somewhere with some values and use references but my sheet is already very large and I need to keep it clutter free

Hi there, I hope somebody here can help me. Maybe it is a very simple solution or else not possible.

Anyway I have 1 column with around 6,000 rows in use (column A)

I have another column with merged cells. (all blank) (Column B)

I would like to make each cell in the merged cells simple equal the value in the first column. This I can do easily using (=a1)

The problem is Excel doesn't seem to be finding the pattern I would like. Because I have merged cells 3 rows in size(Column B) the auto fill is going from =a1 to =a4. I would like it to simply go from =a1 to =a2 to =a3 with my fill.

Sorry if i did not explain this in a great way but I would really really appreciate anybody s help!

Anyway I have 1 column with around 6,000 rows in use (column A)

I have another column with merged cells. (all blank) (Column B)

I would like to make each cell in the merged cells simple equal the value in the first column. This I can do easily using (=a1)

The problem is Excel doesn't seem to be finding the pattern I would like. Because I have merged cells 3 rows in size(Column B) the auto fill is going from =a1 to =a4. I would like it to simply go from =a1 to =a2 to =a3 with my fill.

Sorry if i did not explain this in a great way but I would really really appreciate anybody s help!

I have 30 Plus tabs in my work book. I am trying to auto fill Tab Names to make it easy. Auto fill to me is to drag a cell and have the formating change for the next cell.

To make it easier i have named my cells 1 - 32 I though this would make it easier for auto fill

I am trying to drag this down a column

='1'!A$1:J$1

A1:J1 is a merged cell so there will just be a transfer of the students name on the top of the page.

'1' is the tab name, but when i drag the cell down the column I would like the next cell to say

='2'!A$1:J$1

='3'!A$1:J$1

='4'!A$1:J$1

And so on.

I will need to use this auto fill in other cells also.

Right now it is not auto filling the tab name. i can get it to auto fill and change the A1:J1 if I take off the $ sign. When I take off the $ it will still not work on the Tab name.

Any Help would be great.

thanks

To make it easier i have named my cells 1 - 32 I though this would make it easier for auto fill

I am trying to drag this down a column

='1'!A$1:J$1

A1:J1 is a merged cell so there will just be a transfer of the students name on the top of the page.

'1' is the tab name, but when i drag the cell down the column I would like the next cell to say

='2'!A$1:J$1

='3'!A$1:J$1

='4'!A$1:J$1

And so on.

I will need to use this auto fill in other cells also.

Right now it is not auto filling the tab name. i can get it to auto fill and change the A1:J1 if I take off the $ sign. When I take off the $ it will still not work on the Tab name.

Any Help would be great.

thanks

Attached is my little project that has been I have been working on for days. Ironically, what I need to do is easy.... But I also want dates that auto update correctly, but only for workdays, no sats or suns.

The dates I'm concerned about are in the B column. (Heck provided I get this figured out, I wouldn't even need the A column, and the sheet would be all the better for it)

So for example, say Sunday is the 1st.... I want the first entry (Monday of course) to read 2nd.

Can anyone help me please?

Thank you for saving my sanity once again.

Almost forgot! This if for Excel 2003

Edit: Oh, just noticed, while playing with this before, I added A1 as Year, and B1 as Month ... then used those for reference cells... I did some other stuff and confused everything, so never saved. I reuploaded the file to reflect that change

The dates I'm concerned about are in the B column. (Heck provided I get this figured out, I wouldn't even need the A column, and the sheet would be all the better for it)

So for example, say Sunday is the 1st.... I want the first entry (Monday of course) to read 2nd.

Can anyone help me please?

Thank you for saving my sanity once again.

Almost forgot! This if for Excel 2003

Edit: Oh, just noticed, while playing with this before, I added A1 as Year, and B1 as Month ... then used those for reference cells... I did some other stuff and confused everything, so never saved. I reuploaded the file to reflect that change

Hi Guys,

In my sheet, I often need to add suffixes to a column, sometimes the column will have just numbers, sometimes a mix of letters and numbers.

What I'm trying to do is 'Fill Down' just the suffix, so if a cell contains 'P123' the next in the column might need to be 'P123a' then 'P123b' etc, is there a function in Excel 2007 that will allow me to fill down the suffixes, or if necessary fill down the first part 'P123' then fill down the suffix, instead of filling down the numbers then adding the suffixes manually?

Phil.

In my sheet, I often need to add suffixes to a column, sometimes the column will have just numbers, sometimes a mix of letters and numbers.

What I'm trying to do is 'Fill Down' just the suffix, so if a cell contains 'P123' the next in the column might need to be 'P123a' then 'P123b' etc, is there a function in Excel 2007 that will allow me to fill down the suffixes, or if necessary fill down the first part 'P123' then fill down the suffix, instead of filling down the numbers then adding the suffixes manually?

Phil.

I have column A to fill date, column B to fill in yes, column C is the description of task

Column C is filled with many rows of different tasks.

If i have completed a task listed in the row today, I will fill in the column A with today's date and put a "yes" in column B to indicate that it is done

Every I will need to fill in column A date and column B.

Is there a way that excel can automatically fill up column A date (by using the pc date) whenever the excel detects that column B is filled with "yes"? So in this way, I do not need to type in the date myself. As long as I type in yes in column B, it will have today's date automatically fill in in column A.

Thanks

Column C is filled with many rows of different tasks.

If i have completed a task listed in the row today, I will fill in the column A with today's date and put a "yes" in column B to indicate that it is done

Every I will need to fill in column A date and column B.

Is there a way that excel can automatically fill up column A date (by using the pc date) whenever the excel detects that column B is filled with "yes"? So in this way, I do not need to type in the date myself. As long as I type in yes in column B, it will have today's date automatically fill in in column A.

Thanks

is it possible to use the autofill function to fill in a the columns of a worksheet with weekend dates only?

I have tried but failed any tips would be appreciated to save me typing all the dates for Saturdays and Sundays throughout the year.

Thanks in advance

I have tried but failed any tips would be appreciated to save me typing all the dates for Saturdays and Sundays throughout the year.

Thanks in advance