Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
First day of month
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 Code: -------------------- =DATE(YEAR(NOW()),MONTH(NOW()),1) -------------------- just can't come up with an IF function to put it in the appropriate column. -- uwajes ------------------------------------------------------------------------ uwajes's Profile: http://www.excelforum.com/member.php...o&userid=13424 View this thread: http://www.excelforum.com/showthread...hreadid=538067 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
First day of month
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need cell formula to subtotal gross by month for a quarter | Excel Discussion (Misc queries) | |||
Working days left in the month compared to previous months | Excel Worksheet Functions | |||
Referencing data in different worksheet based on month name | Excel Worksheet Functions | |||
Add one month to the previuos month | Excel Discussion (Misc queries) | |||
Month Year Date Format | Excel Worksheet Functions |