Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Timesheet Problem
I am trying to populate a timesheet with dates depending on the start date
in B2. If B2 is the 16th of the month of Sept, I don't want anything to show up on the calendar for the 31th (blank in other words). Here is the formula I am using, but not working. Help. TIA =IF(($B$2)="","31",IF(EOMONTH(B2,0)=TEXT(($B$2)+RO W()-14,"DDD dd"),"",TEXT(($B$2)+ROW()-14,"DDD dd"))) Greg |
#2
|
|||
|
|||
Hi Greg,
With seed date in cell $B$2 and using rows down to leave the 31st blank. But show all other dates no exceptions for weekends and holidays. EOMONTH is in the Analysis Toolpak (I believe) Use the fill handle to fill down or use Ctrl+D for the range. B2: (seed date) B3: =IF(DAY($B$2+ROW()-2)=31,"",$B$2+ROW()-2) B4: =IF(DAY($B$3+ROW()-2)=31,"",$B$3+ROW()-2) What is that actually for, I can't think of any reason to skip the 31st. Do lockup everything and go home. If instead of blank you wanted to omit the 31st B2: (seed date) B3: =IF(DAY(B2+1)=31, B2+2, B2+1) B4: =IF(DAY(B3+1)=31, B3+2, B3+1) I believe your problem starts with assuming that text and numbers are the same. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "GregR" wrote in message... I am trying to populate a timesheet with dates depending on the start date in B2. If B2 is the 16th of the month of Sept, I don't want anything to show up on the calendar for the 31th (blank in other words). Here is the formula I am using, but not working. Help. TIA =IF(($B$2)="","31",IF(EOMONTH(B2,0)=TEXT(($B$2)+RO W()-14,"DDD dd"),"",TEXT(($B$2)+ROW()-14,"DDD dd"))) Greg |
#3
|
|||
|
|||
David it is for a timesheet. There are only 30 days in Nov, so if B2=nov16,
I want the cell that would be the 31st blank or if b2="", I want the rows to display a generic timesheet 1/16, 2/17......................31. TIA Greg "David McRitchie" wrote in message ... Hi Greg, With seed date in cell $B$2 and using rows down to leave the 31st blank. But show all other dates no exceptions for weekends and holidays. EOMONTH is in the Analysis Toolpak (I believe) Use the fill handle to fill down or use Ctrl+D for the range. B2: (seed date) B3: =IF(DAY($B$2+ROW()-2)=31,"",$B$2+ROW()-2) B4: =IF(DAY($B$3+ROW()-2)=31,"",$B$3+ROW()-2) What is that actually for, I can't think of any reason to skip the 31st. Do lockup everything and go home. If instead of blank you wanted to omit the 31st B2: (seed date) B3: =IF(DAY(B2+1)=31, B2+2, B2+1) B4: =IF(DAY(B3+1)=31, B3+2, B3+1) I believe your problem starts with assuming that text and numbers are the same. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "GregR" wrote in message... I am trying to populate a timesheet with dates depending on the start date in B2. If B2 is the 16th of the month of Sept, I don't want anything to show up on the calendar for the 31th (blank in other words). Here is the formula I am using, but not working. Help. TIA =IF(($B$2)="","31",IF(EOMONTH(B2,0)=TEXT(($B$2)+RO W()-14,"DDD dd"),"",TEXT(($B$2)+ROW()-14,"DDD dd"))) Greg |
#4
|
|||
|
|||
Hi Greg,
In that case what I provided is not what you want. You'd want something like this and you can extend it down as far as you want and start with any date (any day of any month of any year). B2: (seed date) -- fill down from B3 B3: =IF(ISNUMBER(B2), IF(MONTH(B2)=MONTH(B2+1), B2+1,""),B1+1) B4: =IF(ISNUMBER(B3), IF(MONTH(B3)=MONTH(B3+1), B3+1,""),B2+1) --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "GregR" wrote... David it is for a timesheet. There are only 30 days in Nov, so if B2=nov16, I want the cell that would be the 31st blank or if b2="", I want the rows to display a generic timesheet 1/16, 2/17......................31. TIA "GregR" wrote in message... I am trying to populate a timesheet with dates depending on the start date in B2. If B2 is the 16th of the month of Sept, I don't want anything to show up on the calendar for the 31th (blank in other words). Here is the formula I am using, but not working. Help. TIA =IF(($B$2)="","31",IF(EOMONTH(B2,0)=TEXT(($B$2)+RO W()-14,"DDD dd"),"",TEXT(($B$2)+ROW()-14,"DDD dd"))) Greg |
#5
|
|||
|
|||
David, thank you very much..........
Greg "David McRitchie" wrote in message ... Hi Greg, In that case what I provided is not what you want. You'd want something like this and you can extend it down as far as you want and start with any date (any day of any month of any year). B2: (seed date) -- fill down from B3 B3: =IF(ISNUMBER(B2), IF(MONTH(B2)=MONTH(B2+1), B2+1,""),B1+1) B4: =IF(ISNUMBER(B3), IF(MONTH(B3)=MONTH(B3+1), B3+1,""),B2+1) --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "GregR" wrote... David it is for a timesheet. There are only 30 days in Nov, so if B2=nov16, I want the cell that would be the 31st blank or if b2="", I want the rows to display a generic timesheet 1/16, 2/17......................31. TIA "GregR" wrote in message... I am trying to populate a timesheet with dates depending on the start date in B2. If B2 is the 16th of the month of Sept, I don't want anything to show up on the calendar for the 31th (blank in other words). Here is the formula I am using, but not working. Help. TIA =IF(($B$2)="","31",IF(EOMONTH(B2,0)=TEXT(($B$2)+RO W()-14,"DDD dd"),"",TEXT(($B$2)+ROW()-14,"DDD dd"))) Greg |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update Links - Problem | Links and Linking in Excel | |||
Problem with date base units for x axis | Charts and Charting in Excel | |||
Hyperlink to word document problem | Links and Linking in Excel | |||
Paper Tray selection Problem, | Excel Discussion (Misc queries) | |||
File is locked for Editing by user problem | Excel Discussion (Misc queries) |