If function with dates

[Closed]
Report
Posts
2
Registration date
Saturday October 5, 2013
Status
Member
Last seen
October 6, 2013
-
Posts
2
Registration date
Saturday October 5, 2013
Status
Member
Last seen
October 6, 2013
-
I'm building a personal finance tracker, and I'm attempting to get a function to tell me which week of the month I purchased something in. Right now I'm entering the date of purchase in column c and other data in other columns. I want column g to see if the date in column c falls on the 1st to the 7th of the month and if so return a result of "week1", on the 8th to the 14th with a result of "week2" and so on and so forth. I would appreciate any feedback if there is any easier way to do it than I currently am looking at which is: listing every day in the year in column A and then interacting with them thusly (not fully built out yet): =IF(OR(COUNTIF(A77:A83,C81),COUNTIF(A109:A111,C81),COUNTIF(A137:A139,C81)),"week1",IF(OR(COUNTIF(A84:A90,C81),COUNTIF(A140:A145,C81)),"week2"))

Am I going about this in completely the wrong fashion?

Thanks!

2 replies

Posts
42
Registration date
Thursday January 31, 2013
Status
Member
Last seen
April 26, 2014
9
Hi kevinlewis13

Two ways and assuming the dates start in C2. For example if the date in C2 = "27/12/2013".

This will return "Week4 ":

="Week"&CEILING(DAY(C2)/7,1)
and copy down.

Or if you require: "Dec: Week4":

=TEXT(C2,"mmm")&": "&"Week"&CEILING(DAY(C2)/7,1)
and copy down.

Kevin
Posts
2
Registration date
Saturday October 5, 2013
Status
Member
Last seen
October 6, 2013

Works like a dream! Thank you so much!

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!