Fill Dates With Weekdays/workdays Only


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?
TIA
Similar Excel Video Tutorials
Similar Topics
I need to fill a row with only the weekdays (NetworkDays) for any given month
I define the fist day fo the month (say) in A1 as 3/1/2011
then fill row 2 with all workdays for that month, starting in column C using formulas
Thanks
Hi,
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
etc
Thanks,
kilyg
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 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:
DATE WORKDAYS TASK
1Dec 1 AP CLOSE  CAPITAL & POS ITEMS
1Dec 1 POS MONTH END
1Dec 1 HEALTH INSURANCE
2Dec 2 DEPR SYSTEM RUN; INCLSOFTWARE AMORT
2Dec 2 SPECIAL EQUIPMENT
4Dec 4 PRELIMINARY ENERGY REPORT
7Dec 5 AP CLOSE  ALL OTHER ITEMS
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...
Thanks!
I'm familiar with the networkday() function to calculate the number of weekdays between two dates, but is there any way that I can add a certain nmber of weekdays to a date? E.g.,
06/11/2009 + 5 = 11/11/2009
but
06/11/2009 + 5 weekdays = 13/11/2009
The second result is the one i'm after.
Thanks
Stu
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((B43)>=TODAY(),TRUE,FALSE) Format  Fill Color "Light Yellow"
Condition 2 =IF((B42)>=TODAY(),TRUE,FALSE) Format  Fill Color "Yellow"
Condition 3 =IF((B40)>=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!
Hi
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.
=SUM(INT((WEEKDAY(B15{1,2,3,4,5},2)+C15B15)/7))
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?
Cheers
blue
Hi
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.
Examples:
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.
Help!!
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 ?
Thanks
A
Hi everyone.
Is there any way of finding out only weekdays. I want to get all the date starting on monday and ending on friday for entire year.
Hello,
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?
Hi,
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!
Robbert
Hi
I need a formula to tell me how many weekdays between two dates, is this possible?
Cheers
Steve
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 ?
Thanks
Uli
Hello,
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$1A17) 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
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 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
Im just trying have a running worksheet with weekdays or dates across the top that will instuct me what I have coming up for tomorrow the next week and month, in certain areas of my business (that will run down the rows) as that day passes i want the worksheet to keep moving along so that the current date and its activities will be in column B and my businesses different sectors will remain in column A.