Turning dates into weeks?

B

belboy87

New Member
Joined
Apr 9, 2008
Messages
35
I have an excel sheet that has a field called "Decision Date". It is a numerical date (ex: 1/5/09 indicating January 5th 2009).

I need to turn that date into the week that it falls into within a particular quarter of a year. For example:

1/5/09 would be Week 2 (it is in the second week of January, and 2nd week of the quarter)
2/3/09 would be week 6 (6th week of the quarter).

And then I need the formula to start over once each quarter restarts... for example, April 1st would be week 1 (1st week of the second quarter).

Can Excel do this? Convert a date to the week it falls within?
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
oNE WAY

=INT((A1-(DATE(YEAR(A1),MONTH(A1)-MOD(MONTH(A1),3)+1,1)-WEEKDAY(DATE(YEAR(A1),MONTH(A1)-MOD(MONTH(A1),3)+1,1),2)+1)+7)/7)
 
Upvote 0
or.... you could simply use the WEEKNUM() function. :)

(Check you inline help system for WEEKNUM function if you need the syntax)
 
Last edited:
Upvote 0
nbrcrunch said:
or.... you could simply use the WEEKNUM() function. :)

(Check you inline help system for WEEKNUM function if you need the syntax)
Click to expand...

WOuld you care to enlighten us as to how WEEKNUM caters for restarting in a new quarter?
 
Upvote 0
xld said:
WOuld you care to enlighten us as to how WEEKNUM caters for restarting in a new quarter?
Click to expand...

I'm sure I can write three if functions so that as soon as the weeks = value higher than the quarter, I subtract that value of the previous quarter weeks.
 
Upvote 0
belboy87 said:
I'm sure I can write three if functions so that as soon as the weeks = value higher than the quarter, I subtract that value of the previous quarter weeks.
Click to expand...

a) you just said the WEEKNUM worked perfectly, which it doesn't according to your original spec

b) that would be as or more complicated than what I gave
 
Upvote 0
Well, dang, xld, I must admit I only read the part about converting to week number. YOU are "the man" (or "the woman").

As to Quarters

=LOOKUP((MONTH(A1)/4),{0,1,1.75,2.5},{1,2,3,4})

where A1 carries the date the user is seeking the quarter of.
 
Last edited:
Upvote 0
I had forgotten about this one:

=ROUNDUP(MONTH(TargetDate)/3,0)

where "targetdate" is the date you are seeking the quarter of.
 
Upvote 0
You must log in or register to reply here.

Similar threads

CubeMonkey
  • Question
Week Number based on Day. (Based on United States Government Fiscal Year)
Replies
1
Views
518
MDCurry
M
U
  • Solved
Week number in the calendar
Replies
2
Views
828
UmairKamal
U
S
  • Question
Send automated emails based on dates
Replies
3
Views
729
somesoldiers
S
M
  • Question
Excel - Identify number of business days for a specific month within a weekly date range
Replies
3
Views
510
duggie33
D
F
  • Solved
Date Range Period over Period over years
Replies
7
Views
479
flashgordie
F

Forum statistics

Threads
1,215,375
Messages
6,124,583
Members
449,174
Latest member
chandan4057

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top