In its current setup, the user simply types in the first date of the
pay period (the 1st or the 16th in date format) in I10, and all the
corresponding cells auto-populate with the calendar dates that follow
as a result of the formulas I've listed.

Hope this helps to clarify.

Thanks!

On Jun 21, 8:40*pm, "Rick Rothstein \(MVP - VB\)"
wrote:
As I said in my first post, I don't fully understand the structure of your
worksheet and, unfortunately, nothing in your last post has changed that.
What I am having trouble understanding is why the I10 date is changing. Are
you implying by that statement that you are retaining the previous
pay-periods' data on the worksheet and somehow adding the new pay-period's
data to it? That would be about the only thing I can visualize that might
cause what I suggested not to work. Can you post a copy of your workbook
online somewhere so we can see what it looks like? Perhaps that would help
me (and the others who are currently avoiding your thread) to understand
what you are trying to describe.

Rick

wrote in message

...
I thank you for your reply, but unfortunately, it doesn't work for
this as the date in I10 changes with each new pay period. Any other
thoughts?

On Jun 20, 1:22 pm, "Rick Rothstein \(MVP - VB\)"



wrote:
I'm not 100% sure I understand your structure as post; however, you can
filter out the days greater than the end of the month by using a simple IF
function call. On those dates corresponding to day 29, 30 and 31, use a
formula similar to this...

=IF(<DateToTestDATE(YEAR($I10),MONTH($I10)+1,0), "",<YourFormula)

assuming I10 contains a date in the current month. The DateToTest would be
whichever of your U10+1, U10+1, etc. columns corresponded the 29th, 30th
or
31st day positions for the month. What this formula structure will do is
print nothing (the empty string) if the date being tested (probably the
previous columns date plus one) is greater than the last date in the month
(that is what the DATE function call calculates... the zeroeth date of the
next month is the same as the last date of the current month).

Rick

wrote in message

...
Hi,

I am creating a timesheet in Excel, and I am running into 2 issues.

First, our pay periods are the 1st – 15th & the 16th – the end date of
each month. I am using the dates as column headings. I am currently
utilizing 16 columns to accommodate months in which there are 31 days.
The coordinating days of the week are listed in the cells directly
above the date cells.

Using the following formulas works great except whenever there are
less than 31 days in a month, the cells automatically start populating
with dates for the following month, and because I am allowing for the
31st, whenever working in the 1st- the 15th, the 16th populates as
well. I don’t necessarily have to not show columns if they don’t have
a corresponding date for the current pay period, but I would like for
it to return blank cells in these scenarios. Hope this makes sense.

Here are the formulas that I am using to accomplish the days of the
week result:

=VLOOKUP(I9,$BB$98:$BC$104,2,FALSE)
=VLOOKUP(J9,$BB$98:$BC$104,2,FALSE)
=VLOOKUP(K9,$BB$98:$BC$104,2,FALSE) continuing on ending with
=VLOOKUP(X9,$BB$98:$BC$104,2,FALSE)

Here are the formulas to accomplish auto-populating the dates:
Cell I10 contains no formula…simply enter the date to start the date
population

=IF($I10<" / /03",I10+1," / /03")
=IF($I10<" / /03",J10+1," / /03")
=IF($I10<" / /03",K10+1," / /03")
=IF($I10<" / /03",L10+1," / /03")
=IF($I10<" / /03",M10+1," / /03")
=IF($I10<" / /03",N10+1," / /03")
=IF($I10<" / /03",O10+1," / /03")
=IF($I10<" / /03",P10+1," / /03")
=IF($I10<" / /03",Q10+1," / /03")
=IF($I10<" / /03",R10+1," / /03")
=IF($I10<" / /03",S10+1," / /03")
=IF($I10<" / /03",T10+1," / /03")
=IF($I10<" / /03",U10+1," / /03")
=IF($I10<" / /03",V10+1," / /03")
=IF($I10<" / /03",W10+1," / /03")

Secondly, but not as crucial, is there a way using my current set up
to add some conditional formatting that will shade in the columns for
Saturdays and Sundays. I still want the columns able to be utilized as
some of our employees do work on weekends, but I would like the
shading to create division between each week in the pay period.

As you may be able to tell, I am a bit green with all this, so if you
respond, please break it down for me. :0)

Thanks much,
Rondi- Hide quoted text -

- Show quoted text -