| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timesheet Date Populating Problem
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Timesheet Date Populating Problem
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Timesheet Date Populating Problem
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Timesheet Date Populating Problem
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Timesheet Date Populating Problem
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 - |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Timesheet Date Populating Problem
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+1DATE(YEAR($I10),MONTH($I10)+1,0),"",U10+ 1) W10: =IF(U10+2DATE(YEAR($I10),MONTH($I10)+1,0),"",U10+ 2) X10: =IF(DAY(I10)=1,"",IF(U10+3DATE(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 wrote in message ... 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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Timesheet formula problem | Excel Worksheet Functions | |||
| timesheet problem | Excel Discussion (Misc queries) | |||
| Somewhat complex timesheet problem. | Excel Worksheet Functions | |||
| Timesheet problem | Excel Discussion (Misc queries) | |||
| Timesheet Problem | Excel Discussion (Misc queries) | |||
Hybrid Mode
