Timesheet help

[DaddyO]

I am using 2003, and I am trying to fix my employers timesheet. It started
as a basic timesheet where we had to manually fill in each date and shade in
the weekend columns. I have everything worked out so that I can enter the
start date and everything updates from there. My only problem is the months
that have 31 days. I want to have the top two rows empty for that column if
the month is not 31 days. It is currently set up so the first day of the pay
period references the start of payperiod cell, then each day after that is
the previous day +1. The row above picks the day cell and displays the day
of the week. All of the rows pick from the current day and have a
conditional format on a weekday formula to gray out the weekends. I have
tried to reference the previous day and the start day with an IF formula to
have it "" for the short months and +1 for the long months, but it does not
seem to recognize the date format and return what I want. It was suggested
that I try MONTH+1, and this did not seem to work either. Any ideas??????

 

[JLatham]

I assume your pay periods are 1-15 and 15-last?

In any case, try this. Assuming that cell A2 has your start of pay period
date, such as 4/16/08. Then out where you might have 29, 30 and/or 31 (must
not forget February) try a formula such as this in the 3rd from last column
(which in my test was column N, with base/first date of pay period in A2) so
this formula went into N2

=IF(ISERR(DAY(M2+1)),"",IF(DAY(M2+1)<DAY($A2),"",M2+1))
you can adapt that for the row above the numeric date row to display/not
display the day of the week.

Hope that helps some.

 

[DaddyO]

You were absolutely correct with the twice monthly deal. The formula you
provided worked out beautifully, and it works like a charm. I am currious
how that formula works for future reference.

 

[JLatham]

The IF(ISERR( portion handles the situation where more than one of the last 3
days is in the next month. When that occurs, the first not-in-current-month
date gets set to "" and so the math for calculating the next day/date fails
with a #VALUE error. The ISERR test prevents that from interfering with
downstream calculations. The IF DAY(newlycalculated) < DAY(startdate) test
simply relies on the fact that at the end of a month you're going to have
large numbers such as 28, 29, 30 or 31, while at the start of that period
you're going to have medium sized numbers like 15 or 16, so if you roll over
to a new month, the day will be a small number, as 1 or 2 or 3.