Return mm/dd/yy based on 1st Day of Month

[Guest]

Excel2003 ... I have 12 TabSheets (1 for each month ... Jan, Feb, Mar etc)

In each TabSheet I would like a cell result returned (format = mm/dd/yy)
based on the following:

If 1st day of ( Jan ) is a:

Mon ... Return value = 01/07/07
Tue ... Return value = 01/06/07
Wed .. Return value = 01/05/07
Thu ... Return value = 01/04/07
Fri ..... Return value = 01/03/07
Sat .... Return value = 01/09/07
Sun ... Return value = 01/08/07

Note: I will need to repeat formula for subsequent months ... Jan, Feb, Mar
etc ... Thanks ... Kha

 

[Bernard Liengme]

Please: you have 12 "worksheets"
The "tabs" are the little things you click to open a worksheet
How are the worksheets named?
Or is there a cell in each worksheet that tells us which month it is for?
best wishes

 

[Guest]

I'm not sure where you're getting the date from
so I'll assume cell A1 contains the date

With
A1: 01/01/2007

Try this
B1: =8-MOD(WEEKDAY(A1,2),6)+(WEEKDAY(A1,2)>5)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Ummmm......my posted formula only returns the day number you want.

For an actual date, use this:
B1: =A1-DAY(A1)+8-MOD(WEEKDAY(A1,2),6)+(WEEKDAY(A1,2)>5)
(format B1 as a date)

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Looks like you want to return the first Sunday after the 2nd of the month. If
the first day of the month is shown in A1 as Ron suggests then use this
formula

=A1+9-WEEKDAY(A1+1)

 

[Guest]

ALL ... This really turned out to be a dumb post ... Each of my "WorkSheets"
which Bernard so eloquently pointed out are named ... Jan, Feb, Mar etc as
stated in my original post ... If the Month was Jan & I was in the Jan
WorkSheet ... then I wanted a formula in a Cell that would enter mm/dd/yy
based on the beginning day of Jan ... But then I realized as the Calendar
moved ... so would my returned values ... Thing is ... I did not know how to
teardown this post ...

Thanks to all that responded ... Sorry ... Kha