Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dates for month in cell a1 downwards
Hello all
I want to be able to put a date in cell a1 - 1st Jan 2007 and then have a2 downwards automatically fill in the dates of the month excluding the saturdays and sundays??? Any helpers with this thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dates for month in cell a1 downwards
Found it !!!! thanks to Pearson consulting
=IF(WEEKDAY(A4)=7,A4+2,IF(WEEKDAY(A4)=6,A4+3,A4+1) ) pano wrote: Hello all I want to be able to put a date in cell a1 - 1st Jan 2007 and then have a2 downwards automatically fill in the dates of the month excluding the saturdays and sundays??? Any helpers with this thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dates for month in cell a1 downwards
Assuming you always put the first workday of the month in A1 then you can use
this formula in A2 copied down to A23 to give all weekdays in the month =IF(A1="","",IF(MONTH(WORKDAY(A1,1))<MONTH(A1),"" ,WORKDAY(A1,1))) note: WORKDAY is part of Analysis ToolPak add-in "pano" wrote: Hello all I want to be able to put a date in cell a1 - 1st Jan 2007 and then have a2 downwards automatically fill in the dates of the month excluding the saturdays and sundays??? Any helpers with this thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dates for month in cell a1 downwards
Fill in the first 2 days and then select your range including the 2 days you
typed and try Edit - Fill - Series - weekdays and this may do the trick. "pano" wrote: Hello all I want to be able to put a date in cell a1 - 1st Jan 2007 and then have a2 downwards automatically fill in the dates of the month excluding the saturdays and sundays??? Any helpers with this thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dates for month in cell a1 downwards
Hmm cant use the analysis Toolpak addin, this is a work toughbook and it is LOCKED down to billy oh by the IT Dept On Jan 29, 9:22 pm, Mike wrote: Fill in the first 2 days and then select your range including the 2 days you typed and try Edit - Fill - Series - weekdays and this may do the trick. "pano" wrote: Hello all I want to be able to put a date in cell a1 - 1st Jan 2007 and then have a2 downwards automatically fill in the dates of the month excluding the saturdays and sundays??? Any helpers with this thanks- Hide quoted text -- Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dates for month in cell a1 downwards
In A2
=IF(A1="","",IF(WEEKDAY(A1+1,2)<6,IF(MONTH(A1+1)=M ONTH($A$1),A1+1,""),IF(MON TH(A1+3)=MONTH($A$1),A1+3,""))) copy down. -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "pano" wrote in message ps.com... Hello all I want to be able to put a date in cell a1 - 1st Jan 2007 and then have a2 downwards automatically fill in the dates of the month excluding the saturdays and sundays??? Any helpers with this thanks |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dates for month in cell a1 downwards
Thanks Bob that does the trick On Jan 29, 10:00 pm, "Bob Phillips" wrote: In A2 =IF(A1="","",IF(WEEKDAY(A1+1,2)<6,IF(MONTH(A1+1)=M ONTH($A$1),A1+1,""),IF(MO*N TH(A1+3)=MONTH($A$1),A1+3,""))) copy down. -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "pano" wrote in glegroups.com... Hello all I want to be able to put a date in cell a1 - 1st Jan 2007 and then have a2 downwards automatically fill in the dates of the month excluding the saturdays and sundays??? Any helpers with this thanks- Hide quoted text -- Show quoted text - |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dates for month in cell a1 downwards
Mike's suggestion doesn't use the Analysis toolpak.
pano wrote: Hmm cant use the analysis Toolpak addin, this is a work toughbook and it is LOCKED down to billy oh by the IT Dept On Jan 29, 9:22 pm, Mike wrote: Fill in the first 2 days and then select your range including the 2 days you typed and try Edit - Fill - Series - weekdays and this may do the trick. "pano" wrote: Hello all I want to be able to put a date in cell a1 - 1st Jan 2007 and then have a2 downwards automatically fill in the dates of the month excluding the saturdays and sundays??? Any helpers with this thanks- Hide quoted text -- Show quoted text - -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split a cell diagonally in excell - a calendar -2 dates in 1 cell | Excel Discussion (Misc queries) | |||
insert date | Excel Worksheet Functions | |||
Enter 2 dates in same excel cell | Excel Discussion (Misc queries) | |||
Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |