Home |
Search |
Today's Posts |
#1
|
|||
|
|||
4 or 5 week months?
I am designing a new overtime form and our overtime policy states that each
form must be from the First Monday of the month until the week commencing on the last Monday of the month. I would like to have it so that if you enter the start date of the overtime form (in field B2) at the top of the page the 5 columns all headed with dates are automatically populated. This is easy enough with the first column (C8) =B2, and all later dates =C8+7, =D8+7, etc. The problem is... If I enter the start period as yesterday 4/9/2005 (sorry, I'm English, so may have the date the wrong way round for the Americans!) then the final Monday of the 5 week period will be 2/10/2005, in the following month. In this case, instead of automatically updating to 2/10/2005, I would like the field to remain blank and only 4 of the columns to be headed. Is this possible? Many thanks in advance! |
#2
|
|||
|
|||
On Tue, 6 Sep 2005 09:22:12 -0700, "Matt_hull1979"
wrote: I am designing a new overtime form and our overtime policy states that each form must be from the First Monday of the month until the week commencing on the last Monday of the month. I would like to have it so that if you enter the start date of the overtime form (in field B2) at the top of the page the 5 columns all headed with dates are automatically populated. This is easy enough with the first column (C8) =B2, and all later dates =C8+7, =D8+7, etc. The problem is... If I enter the start period as yesterday 4/9/2005 (sorry, I'm English, so may have the date the wrong way round for the Americans!) then the final Monday of the 5 week period will be 2/10/2005, in the following month. In this case, instead of automatically updating to 2/10/2005, I would like the field to remain blank and only 4 of the columns to be headed. Is this possible? Many thanks in advance! Perhaps this will help: With any date in B2, the first Monday of that month is given by the formula: =B2-DAY(B2)+8-WEEKDAY(B2-DAY(B2)+6) (This may not be necessary, but might be easier than ensuring the date you enter in B2 is always the first Monday). To ensure, as you copy down the column, that the field remains blank, there are several options. 1. Change your formula to read: D8: =IF(MONTH(C8+7)=MONTH($C$8),C8+7,"") or, you could use conditional formatting. Highlight the entire range with C8 the active cell. Format/Conditional Formatting/Formula Is: =MONTH(C8)<MONTH($C$8) Then Format the font color to white (or whatever the background color is). --ron |
#4
|
|||
|
|||
Hi Matt,
Write into cell C8: =IF(MONTH($B$2)=MONTH($B$2+(COLUMN()-3)*7),$B$2+(COLUMN()-3)*7,"") and copy this to D8:F8. HTH, Bernd |
#5
|
|||
|
|||
It should be possible. In D8, type the following formula:
=if(month($b2)<month((c8+7)),"",c8+7) Compare the month of the start date to the month of the previous column heading plus 7 days. If they are not equal, then do nothing (""). Otherwise, take the previous column heading and add 7 days to it. I wrote the formula so you could copy it to the remaining 3 column headings. Best wishes. Scott "Matt_hull1979" wrote: I am designing a new overtime form and our overtime policy states that each form must be from the First Monday of the month until the week commencing on the last Monday of the month. I would like to have it so that if you enter the start date of the overtime form (in field B2) at the top of the page the 5 columns all headed with dates are automatically populated. This is easy enough with the first column (C8) =B2, and all later dates =C8+7, =D8+7, etc. The problem is... If I enter the start period as yesterday 4/9/2005 (sorry, I'm English, so may have the date the wrong way round for the Americans!) then the final Monday of the 5 week period will be 2/10/2005, in the following month. In this case, instead of automatically updating to 2/10/2005, I would like the field to remain blank and only 4 of the columns to be headed. Is this possible? Many thanks in advance! |
#6
|
|||
|
|||
Hi,
For the column header for the 5th column (G8, I guess), use the formula, =IF(MONTH(F8+7)MONTH(F8),"",F8+7) Regards, B.R. Ramachandran "Matt_hull1979" wrote: I am designing a new overtime form and our overtime policy states that each form must be from the First Monday of the month until the week commencing on the last Monday of the month. I would like to have it so that if you enter the start date of the overtime form (in field B2) at the top of the page the 5 columns all headed with dates are automatically populated. This is easy enough with the first column (C8) =B2, and all later dates =C8+7, =D8+7, etc. The problem is... If I enter the start period as yesterday 4/9/2005 (sorry, I'm English, so may have the date the wrong way round for the Americans!) then the final Monday of the 5 week period will be 2/10/2005, in the following month. In this case, instead of automatically updating to 2/10/2005, I would like the field to remain blank and only 4 of the columns to be headed. Is this possible? Many thanks in advance! |
#7
|
|||
|
|||
Wow, more responses that I expected.
Thanks a lot guys - I didn't realise that there was a MONTH function that would have made it easier! "Sandy Mann" wrote: Matt, If I understand you correctly in G8 enter the formula: =IF(MONTH(F8+7)=MONTH(C8),F8+7,"") -- HTH Sandy Replace@mailinator with @tiscali.co.uk "Matt_hull1979" wrote in message ... I am designing a new overtime form and our overtime policy states that each form must be from the First Monday of the month until the week commencing on the last Monday of the month. I would like to have it so that if you enter the start date of the overtime form (in field B2) at the top of the page the 5 columns all headed with dates are automatically populated. This is easy enough with the first column (C8) =B2, and all later dates =C8+7, =D8+7, etc. The problem is... If I enter the start period as yesterday 4/9/2005 (sorry, I'm English, so may have the date the wrong way round for the Americans!) then the final Monday of the 5 week period will be 2/10/2005, in the following month. In this case, instead of automatically updating to 2/10/2005, I would like the field to remain blank and only 4 of the columns to be headed. Is this possible? Many thanks in advance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for If Term is on Certain Date then Count | Excel Worksheet Functions | |||
Why "datedif" function results sometimes negative numbers? | Excel Worksheet Functions | |||
user defined function | Excel Worksheet Functions | |||
Line or bar graphs for tracking stocks profit and loss. | Charts and Charting in Excel | |||
Weekly data allocated to months | Excel Worksheet Functions |