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