if function with dates Closed

Question

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!

Kevin@Radstock · Answer

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

kevinlewis13 · Answer

Works like a dream!  Thank you so much!

If function with dates

2 responses

Similar discussions

Subscribe To Our Newsletter!