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