Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return Matched Numeric Values across Rows | Excel Worksheet Functions | |||
Return across Row Numeric Values Matching EXACT Month & Year for Criteria | Excel Worksheet Functions | |||
Return a cell value based on specific combinations of cells in an array | Excel Worksheet Functions | |||
HELP with this function | Excel Worksheet Functions | |||
Want to return a value based on a whether a number is within a ra. | Excel Worksheet Functions |