Prev: Get cell valueNext: open from sharepoint From: George on 30 Mar 2010 12:19 Good Day, I've search for the solution to this including Cpearson's site but can't figure this out..Our Fiscal year begins April 1st every year regardless of the day of the week it falls on. So here's my delima, if it falls on any day other than a Monday I need to be able to recognize a short week as week 1 ending on Sunday and week 2 beginning on the 1st Monday after and each week following run Monday - Sunday. Example for this year Thurs,4-1-2010 thru Sun 4-4-2010 = Week 1 Mon,4-5-2010 thru Sunday,4-11-2010 = Week 2 and so on Thank you in advance for taking time to look at my issue. Respectfully, George From: Rick Rothstein on 30 Mar 2010 12:31 Can you clarify your question for us? I'm not sure what you want us to help you with. Are you looking to put the date ranges in columns (if so, which ones)? Or are you looking to know which week number a given date is in? Or perhaps something else? -- Rick (MVP - Excel) "George" wrote in message news:D5BFC29F-B2A8-4A68-BEDF-AA81BB0E2D2F(a)microsoft.com...> Good Day, > > I've search for the solution to this including Cpearson's site but can't > figure this out..Our Fiscal year begins April 1st every year regardless of > the day of the week it falls on. So here's my delima, if it falls on any > day > other than a Monday I need to be able to recognize a short week as week 1 > ending on Sunday and week 2 beginning on the 1st Monday after and each > week > following run Monday - Sunday. > > Example for this year > > Thurs,4-1-2010 thru Sun 4-4-2010 = Week 1 > Mon,4-5-2010 thru Sunday,4-11-2010 = Week 2 > and so on > > Thank you in advance for taking time to look at my issue. > Respectfully, > George From: Bob Phillips on 30 Mar 2010 13:16 Try this =INT((H1-(DATE(2010,4,1)-WEEKDAY(DATE(2010,4,1),2)+1)+7)/7) -- HTH Bob "George" wrote in message news:D5BFC29F-B2A8-4A68-BEDF-AA81BB0E2D2F(a)microsoft.com...> Good Day, > > I've search for the solution to this including Cpearson's site but can't > figure this out..Our Fiscal year begins April 1st every year regardless of > the day of the week it falls on. So here's my delima, if it falls on any > day > other than a Monday I need to be able to recognize a short week as week 1 > ending on Sunday and week 2 beginning on the 1st Monday after and each > week > following run Monday - Sunday. > > Example for this year > > Thurs,4-1-2010 thru Sun 4-4-2010 = Week 1 > Mon,4-5-2010 thru Sunday,4-11-2010 = Week 2 > and so on > > Thank you in advance for taking time to look at my issue. > Respectfully, > George From: Herbert Seidenberg on 30 Mar 2010 14:37 Excel 2007 Fiscal week Ditto Bob http://www.mediafire.com/file/htqz4gmm1mj/03_30_10a.xlsx From: George on 31 Mar 2010 13:20 Gary, Thank You .....Works Perfect.. "Gary Brown" wrote: > Assuming the 'Start' year is in Cell A1 and the date you want to check is in > A2... > =ROUNDUP(((A2-MOD(A2-2,7)-DATE(\$A\$1,4,1))/7)+1,0) > > So... > Cell A1 = 2010 > Cell A2 = 04/01/2010 > The formula above will return a '1' > > -- > Hope this helps. > If it does, please click the Yes button. > Thanks in advance for your feedback. > Gary Brown > > > > "George" wrote: > > > Good Day, > > > > I've search for the solution to this including Cpearson's site but can't > > figure this out..Our Fiscal year begins April 1st every year regardless of > > the day of the week it falls on. So here's my delima, if it falls on any day > > other than a Monday I need to be able to recognize a short week as week 1 > > ending on Sunday and week 2 beginning on the 1st Monday after and each week > > following run Monday - Sunday. > > > > Example for this year > > > > Thurs,4-1-2010 thru Sun 4-4-2010 = Week 1 > > Mon,4-5-2010 thru Sunday,4-11-2010 = Week 2 > > and so on > > > > Thank you in advance for taking time to look at my issue. > > Respectfully, > > George  |  Next  |  Last Pages: 1 2 Prev: Get cell valueNext: open from sharepoint