Can anyone help me with this excel function?

Question

I want a cell to return "40" if the day in another cell (which contains a date) is in the first week of any month.

I imagine this would be an IF() function, but I can't figure it out.

Answer 1

it depends what you mean by the first week.  If you're just talking about, does the date fall within the first 7 days of the month, then it's just a simple IF statement of

(assuming you're date is in A1)

=IF(DAY(A1)<=7, 40, 0)

but if by first week you mean does it fall within the first Monday, Tuesday, etc of the month then it gets a bit more complicated.  The below formula should work (and it assumes the first day of the week is a Monday).

=IF(DAY(A1)<=7, IF(WEEKDAY(A1, 2)-DAY(A1)>=0, 40, 0), 0)

first we check to see if the date is within the first 7 days of the months because obviously the 8th, 9th etc of month could never be in the first week of a month.

then we take the value of the weekday and subtract the day from it.  If it's >= 0, we've found a date that falls in the first week and we'll display the 40, otherwise we'll display a 0.

the weekday function assigns a value to monday, tuesday, etc of 1, 2, 3, etc.  If you want your first day of the week to be considered Sunday, change the 2nd parameter to a 1 (weekday(a1, 1)

formula seems to work on the few dates I checked but you might want check it in detail before turning in a report to your boss first :-)))))

Answer 2

I'm not sure what you mean by the first week, but I'll take a crack at it anyway.  :)  Just replace "TODAY()" with the appropriate date.  Hope this helps.

IF(DAY(TODAY()<8),"40","Not the first week")