Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Is it possible to grab just the Mondays-Thursdays of every month?
I am creating a spreadsheet to keep track of dentist office productivity. In
one box, I want to allow the user to enter the month and have the workdates change accordingly. For example, our office works Mondays through Thursdays. In 2005, the first Monday in January is the 3rd. How could I change the dates to read 3, 4, 5, 6, then skip to 10, 11, 12, 13, then skip until all the M-Th of the month are included. These dates should change if the user types in a different month. Thank you for your help. Arlen |
#2
|
|||
|
|||
Arlen
Check out the =WEEKDAY function in Excel. Alex "Arlen" wrote: I am creating a spreadsheet to keep track of dentist office productivity. In one box, I want to allow the user to enter the month and have the workdates change accordingly. For example, our office works Mondays through Thursdays. In 2005, the first Monday in January is the 3rd. How could I change the dates to read 3, 4, 5, 6, then skip to 10, 11, 12, 13, then skip until all the M-Th of the month are included. These dates should change if the user types in a different month. Thank you for your help. Arlen |
#3
|
|||
|
|||
The MOD function is often used to take care of this type of issue.
MOD(cell address,7) will produce... 0 for Satuday 1 for Sunday 2 for Monday 3 for Tuesday 4 for Wednesday 5 for Thursday 6 for Friday if you put the starting date in cell A1, the following formula in cell A2 should work for Monday (2) through Thursday (5). =IF(OR(MOD(A1,7)<2,MOD(A1,7)=5),A1+CHOOSE(MOD(A1,7 )+1,2,1,0,6,5,4,3),A1+1) In English... If A1 is less than Monday OR A1 is equal to Thursday then Add enough days the date in A1 to get to the following Monday [this part takes into account if you, for example, plop a hard-coded date of a Friday, Saturday or Sunday into the middle of the formulas], otherwise add 1 day to the date in A1. Hope this helps, Sincerely, Gary Brown "Arlen" wrote: I am creating a spreadsheet to keep track of dentist office productivity. In one box, I want to allow the user to enter the month and have the workdates change accordingly. For example, our office works Mondays through Thursdays. In 2005, the first Monday in January is the 3rd. How could I change the dates to read 3, 4, 5, 6, then skip to 10, 11, 12, 13, then skip until all the M-Th of the month are included. These dates should change if the user types in a different month. Thank you for your help. Arlen |
#4
|
|||
|
|||
Good point about the 'Weekday' function
Change the formula below to read... =IF(OR(WEEKDAY(A1)<2,WEEKDAY(A1)=5),A1+CHOOSE(WEEK DAY(A1)+1,2,1,0,6,5,4,3),A1+1) HTH, Gary Brown "Gary Brown" wrote: The MOD function is often used to take care of this type of issue. MOD(cell address,7) will produce... 0 for Satuday 1 for Sunday 2 for Monday 3 for Tuesday 4 for Wednesday 5 for Thursday 6 for Friday if you put the starting date in cell A1, the following formula in cell A2 should work for Monday (2) through Thursday (5). =IF(OR(MOD(A1,7)<2,MOD(A1,7)=5),A1+CHOOSE(MOD(A1,7 )+1,2,1,0,6,5,4,3),A1+1) In English... If A1 is less than Monday OR A1 is equal to Thursday then Add enough days the date in A1 to get to the following Monday [this part takes into account if you, for example, plop a hard-coded date of a Friday, Saturday or Sunday into the middle of the formulas], otherwise add 1 day to the date in A1. Hope this helps, Sincerely, Gary Brown "Arlen" wrote: I am creating a spreadsheet to keep track of dentist office productivity. In one box, I want to allow the user to enter the month and have the workdates change accordingly. For example, our office works Mondays through Thursdays. In 2005, the first Monday in January is the 3rd. How could I change the dates to read 3, 4, 5, 6, then skip to 10, 11, 12, 13, then skip until all the M-Th of the month are included. These dates should change if the user types in a different month. Thank you for your help. Arlen |
#5
|
|||
|
|||
THank you very much, Gary and Alex. I'm trying it out right now (11:23 PST)
"Gary Brown" wrote: Good point about the 'Weekday' function Change the formula below to read... =IF(OR(WEEKDAY(A1)<2,WEEKDAY(A1)=5),A1+CHOOSE(WEEK DAY(A1)+1,2,1,0,6,5,4,3),A1+1) HTH, Gary Brown "Gary Brown" wrote: The MOD function is often used to take care of this type of issue. MOD(cell address,7) will produce... 0 for Satuday 1 for Sunday 2 for Monday 3 for Tuesday 4 for Wednesday 5 for Thursday 6 for Friday if you put the starting date in cell A1, the following formula in cell A2 should work for Monday (2) through Thursday (5). =IF(OR(MOD(A1,7)<2,MOD(A1,7)=5),A1+CHOOSE(MOD(A1,7 )+1,2,1,0,6,5,4,3),A1+1) In English... If A1 is less than Monday OR A1 is equal to Thursday then Add enough days the date in A1 to get to the following Monday [this part takes into account if you, for example, plop a hard-coded date of a Friday, Saturday or Sunday into the middle of the formulas], otherwise add 1 day to the date in A1. Hope this helps, Sincerely, Gary Brown "Arlen" wrote: I am creating a spreadsheet to keep track of dentist office productivity. In one box, I want to allow the user to enter the month and have the workdates change accordingly. For example, our office works Mondays through Thursdays. In 2005, the first Monday in January is the 3rd. How could I change the dates to read 3, 4, 5, 6, then skip to 10, 11, 12, 13, then skip until all the M-Th of the month are included. These dates should change if the user types in a different month. Thank you for your help. Arlen |
#6
|
|||
|
|||
On Tue, 4 Jan 2005 10:47:04 -0800, "Arlen"
wrote: I am creating a spreadsheet to keep track of dentist office productivity. In one box, I want to allow the user to enter the month and have the workdates change accordingly. For example, our office works Mondays through Thursdays. In 2005, the first Monday in January is the 3rd. How could I change the dates to read 3, 4, 5, 6, then skip to 10, 11, 12, 13, then skip until all the M-Th of the month are included. These dates should change if the user types in a different month. Thank you for your help. Arlen If you put some date in the month in A1, then the following formula in A2 will give the first Monday of that month: A2: =A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+6) Then in A3: =A2+1 and copy/drag to A5 giving A4: =A3+1 A5: =A4+1 Then in A6: =IF(ISNUMBER(A2),IF(MONTH(A2+7)=MONTH($A$1),A2+7," "),"") and copy/drag down to A20 --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table - Group by Month - Show Items with no data | Excel Discussion (Misc queries) |