Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fiscal Year Week Number Calculation with a twist....
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fiscal Year Week Number Calculation with a twist....
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 ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fiscal Year Week Number Calculation with a twist....
Try this
=INT((H1-(DATE(2010,4,1)-WEEKDAY(DATE(2010,4,1),2)+1)+7)/7) -- HTH Bob "George" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fiscal Year Week Number Calculation with a twist....
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fiscal Year Week Number Calculation with a twist....
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fiscal Year Week Number Calculation with a twist....
Hmm, I don't see any posting in this thread by "Gary Brown"... am I the only
one not seeing that response? -- Rick (MVP - Excel) "George" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fiscal Year Week Number Calculation with a twist....
Nope, I don't see it either. This seems to happen a lot, I often see a guy
thanking a response when I gave the self same response hours earlier. It seems to be something to do with the Microsoft web forum. -- HTH Bob "Rick Rothstein" wrote in message ... Hmm, I don't see any posting in this thread by "Gary Brown"... am I the only one not seeing that response? -- Rick (MVP - Excel) "George" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
fiscal year calculation | Excel Discussion (Misc queries) | |||
mmb- fiscal year calculation-- | Excel Worksheet Functions | |||
Week Number - Fiscal Year | Excel Worksheet Functions | |||
How to get month number for fiscal year starting in Oct. | Excel Worksheet Functions | |||
Fiscal Year Calculation | Excel Worksheet Functions |