Stop date formula based on end on month

[DawnAZ]

Hello everyone....

I have a couple of different issues that I am trying to work out. I have attached a copy of my spreadsheet because I am having difficulty explaining what I need without going on for many pages. I need B4 of WK1 to be the beginning date which automatically fills in the corresponding dates to each worksheet stopping at WK5. Currently the date continues into the next month which I do not want it to do. I need it to stop at the last day of the month depending on whatever month is entered. I also need the cells above the date to fill in the corresponding week day. Is there a way to do this?

My current formula is simple as I am an Excel beginner (obviously):
B4 (is the manually input month)
B8=B4
D8=B4+1
F8=D8+1 as so on

Wk2 is as follows
B4='WK1 , 2012'!N8+1 This continues to WK5. I need an IF formula that sates if Xcell is the end month to stop. The same for the week day. Is this possible? I am terribly sorry for being so unfamiliar with the terminology and formulas. I would greatly appreciate any help I can get on this.

[Cutter]

To get you on the right track (maybe) put this in cell D8 of the Wk 5 sheet and copy into other cells to the right:

=IF(B8="","",IF(B8+1>EOMONTH(B8,0),"",B8+1))
and, in B4 of Wk 5 sheet this
=IF('WK4 , 2012'!N8+1>EOMONTH('WK4 , 2012'!N8,0),"",'WK4 , 2012'!N8+1)

BTW: Note that some of your B4 cells did not have the +1 at the end of their formulas.

[DawnAZ]

I will post later if this works. I truly appreciate your help and thank you!

[DawnAZ]

Cutter, the formula that I ended up with that worked for me was this:

B8=B4
D8=IF(B8+1>EOMONTH(B4,0), "", B8+1)
F8=IF(D8+1>EOMONTH($B$4,0), "", D8+1) then I copied and pasted this formula into the necessary cells in each WK
For the days to correspond to the date entered I used
B7=+B8
D7=+D8 and so forth and just changed the date format.

You are awesome! I had all these terrible "hidden" cells and rows trying to figure out how to do this. I'm glad I posted on this forum, you were great help!

[Cutter]

Happy to be of some help to you. Don't forget to mark your thread as SOLVED (instructions found by clicking Forum Rules @ top of page - see rule #9).

[DawnAZ]

Any sugestion on how to set up my days of the week to start on sunday and end on saturday while still keeping the current formula? Eample: if B8 is the beginning of the month and starts on Thursday then
D8=Fri
F8=Sat
H8=""
J8=""
L8=""
N8=""

Next WK (B8) would begin on Sunday????

I swear I will not volunteer to do this again lol

[Cutter]

I don't follow what you're after. But utilizing the WEEKDAY() function within an IF() would be the way to test what day it is and provide a result accordingly.

http://www.excelfunctions.net/Weekday-Function.html

If you have trouble implementing it then upload your amended file showing the result you want.

[DawnAZ]

I can't get this right.....

[Cutter]

On WK1, 2012
In B8: =IF(WEEKDAY(B4)=2,B4,"")
In D8: =IF(B8<>"",B8+1,IF(WEEKDAY($B4)=3,$B4,""))
In F8: =IF(D8<>"",D8+1,IF(WEEKDAY($B4)=4,$B4,""))
In H8: =IF(F8<>"",F8+1,IF(WEEKDAY($B4)=5,$B4,""))
In J8: =IF(H8<>"",H8+1,IF(WEEKDAY($B4)=6,$B4,""))
In L8: =IF(J8<>"",J8+1,IF(WEEKDAY($B4)=7,$B4,""))
In N8: =IF(L8<>"",L8+1,IF(WEEKDAY($B4)=1,$B4,""))

[DawnAZ]

I am receiving a "#value! error in WK5 N8

=IF(L8+1>EOMONTH($B$4,0),"",L8+1)

[Cutter]

You haven't implemented the IF() tests that I provided earlier. The #VALUE is showing up because the formula is adding 1 to the previous cell's value which is "".

So, those cells need to be like this (for L8): =IF(J8="","",IF(J8+1>EOMONTH($B$4,0), "", J8+1))

You also need to correct cells B4 on WK3 to WK5 (as I mentioned in post #2).

WK 5 cell B4 should also have the EOMONTH() test for that one occasion when Feb 1 starts on a Monday in a non-leap year.

[DawnAZ]

The lightbulb finally came on :-) Thank you for your help and patience.

[Cutter]

You're welcome. I'm glad you got it working.

[UTS]

it for all months including February
=IF(B6="","",IF(B6+1>EOMONTH($B$6,0), "", B6+1))

[UTS]

Hello everyone....

I have a couple of different issues that I am trying to work out. I have attached a copy of my spreadsheet because I am having difficulty explaining what I need without going on for many pages. I need B4 of WK1 to be the beginning date which automatically fills in the corresponding dates to each worksheet stopping at WK5. Currently the date continues into the next month which I do not want it to do. I need it to stop at the last day of the month depending on whatever month is entered. I also need the cells above the date to fill in the corresponding week day. Is there a way to do this?

My current formula is simple as I am an Excel beginner (obviously):
B4 (is the manually input month)
B8=B4
D8=B4+1
F8=D8+1 as so on

Wk2 is as follows
B4='WK1 , 2012'!N8+1 This continues to WK5. I need an IF formula that sates if Xcell is the end month to stop. The same for the week day. Is this possible? I am terribly sorry for being so unfamiliar with the terminology and formulas. I would greatly appreciate any help I can get on this.

it for all months including February
=IF(B6="","",IF(B6+1>EOMONTH($B$6,0), "", B6+1))