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

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

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

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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Ken" wrote in message
...
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

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

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


"Ken" wrote:

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

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

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


"Ron Coderre" wrote:

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


"Ken" wrote:

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

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

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)

"Ron Coderre" wrote:

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


"Ken" wrote:

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

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

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

"daddylonglegs" wrote:

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)

"Ron Coderre" wrote:

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


"Ken" wrote:

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