Timesheet Problem

  • Thread starter Thread starter GregR
  • Start date Start date
G

GregR

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)+ROW()-14,"DDD
dd"),"",TEXT(($B$2)+ROW()-14,"DDD dd")))

Greg
 
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.
 
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
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.
 
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)
 
David, thank you very much..........

Greg
David McRitchie said:
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)
Click to expand...
 
Back
Top