First day of month

[uwajes]

I'm working on an expense report that will have a sheet for each week. There will be 7 columns for the 7 days for each week. And up to 5 sheets for up to 5 weeks per month. In the first sheet, I'd like for the title of the cell in each column to be the date (Mon., 5/1/06). However, if the first day of the week is on Monday as it was for May, then I don't want anything in the Sunday column. Make any sense?

Anyway I can do this with a formula?

I can come up with the first day of the month fine

Please Login or Register  to view this content.

just can't come up with an IF function to put it in the appropriate column.

[Dave O]

I was able to work this out- first, my solution does NOT use the NOW()
function because this value will change from day to day (actually, from
second to second) and will skew your results later on in the month.

Instead, I suggest you designate one cell as the "seed" cell, and in
that cell enter a date such as 5/1/2006. (Note: this date must be an
Excel readable date.) Then set up the daily columns on the Week 1
sheet. In my example I used A2 as the "seed" date, A4 ~ G4 as the day
labels (Sunday, Monday... thru Saturday), and A5 ~ G5 as the date
cells. The formula in A5 is
=IF(WEEKDAY($A$2)=1,$A$2,"")
Translation: If the weekday number, 1 thru 7, of the seed date is 1
(meaning Sunday), show the seed date, else show a blank.
The formula in B5, Monday, is
=IF(WEEKDAY($A$2)=2,$A$2,IF(A5<>"",A5+1,""))
This formula also looks for the weekday number of the seed date and
shows that date if it is Monday; if that test fails, it adds one day to
the entry in the previous day if it is not blank.
The formula for Tuesday, cell C6 is:
=IF(WEEKDAY($A$2)=3,$A$2,IF(B5<>"",B5+1,""))
and remaining formulas are
=IF(WEEKDAY($A$2)=4,$A$2,IF(C5<>"",C5+1,""))
=IF(WEEKDAY($A$2)=5,$A$2,IF(D5<>"",D5+1,""))
=IF(WEEKDAY($A$2)=6,$A$2,IF(E5<>"",E5+1,""))
=IF(WEEKDAY($A$2)=7,$A$2,IF(F5<>"",F5+1,""))

On sheets for subsequent weeks you can simply reference the last column
on the prior week's sheet and add 1.

Note: the WEEKDAY formula can be configured to show Sunday as weekday
#1 (as shown above), Monday as weekday #1, or Monday as weekday #0
(with Saturday as day 6). You'll need to revise the formulas above if
you prefer to see the calendar as Monday thru Sunday, etc.