Timesheet Date Populating Problem

R

rondit

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
 
R

Rick Rothstein \(MVP - VB\)

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(<DateToTest>>DATE(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


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
 
R

rondit

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?
 
R

Rick Rothstein \(MVP - VB\)

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


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?
 
R

rondit

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!
 
R

Rick Rothstein \(MVP - VB\)

In my first post, I don't think I explained what I wanted you to do very
well. Try this experiment on a blank sheet so you can see what I was
attempting to tell you. We will use I10 to hold your first or sixteenth of
the month date (and only those dates). Put the formula =I10+1 in J10 and
copy it across to U10 (so that U10 contains the formula =T10+1), then put
the following formulas in the indicated cells...

V10: =IF(U10+1>DATE(YEAR($I10),MONTH($I10)+1,0),"",U10+1)

W10: =IF(U10+2>DATE(YEAR($I10),MONTH($I10)+1,0),"",U10+2)

X10:
=IF(DAY(I10)=1,"",IF(U10+3>DATE(YEAR($I10),MONTH($I10)+1,0),"",U10+3))

Okay, put various start of pay-period dates in I10 and watch how the cells
(especially at the end) change. Also note how it adjust for leap years in
February. Does that help you any?

Rick


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!
 
R

rondit

Thank you so much...that worked, and I was able to implement it to the
timesheet. One little problem...any cells that are now blank (as they
should be), the cells above with the corresponding days of the week
are now showing #VALUE!. How can I resolve this?

I can't tell you how much I appreciate your help on this...as I'm sure
you can tell, I am no programmer, and this has been making me nuts for
days!

Rondi
 
R

Rick Rothstein \(MVP - VB\)

I'm going to use a shortcut (mainly because I don't know what your formulas
are)... let's say you are looking at W9 which has a formula that refers to
W10... for the shortcut, I am using...

=FORMULA(W10)

for the contents of W9. Change W9 to this...

=IF(W10="","",FORMULA(W10))

Do this for each cell that can refer to a blank cell (I'm assuming that is
V9, W9 and X9) and replace the FORMULA(W10) example formula with the whole
formula (minus the equal sign) that is currently in the cell. What the IF
function is doing is looking to see if the contents of W10 is empty... if it
is, it displays an empty string for itself; if not, it evaluates the formula
after the last comma and displays its result.

Rick


Thank you so much...that worked, and I was able to implement it to the
timesheet. One little problem...any cells that are now blank (as they
should be), the cells above with the corresponding days of the week
are now showing #VALUE!. How can I resolve this?

I can't tell you how much I appreciate your help on this...as I'm sure
you can tell, I am no programmer, and this has been making me nuts for
days!

Rondi
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Supressing a #VALUE! error returned? 3
Date format. 1
Problem formatting date as "dd/mm/yyyy" 2
Multi-Conditional IF Statements 3
conditional formula with lots of conditions 8
Adding columns to get correct results 1
Adding columns with dates - 2
Using ranges with Visual Basic, stuck... 6

Top