Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Timesheet help
I am using 2003, and I am trying to fix my employers timesheet. It started
as a basic timesheet where we had to manually fill in each date and shade in the weekend columns. I have everything worked out so that I can enter the start date and everything updates from there. My only problem is the months that have 31 days. I want to have the top two rows empty for that column if the month is not 31 days. It is currently set up so the first day of the pay period references the start of payperiod cell, then each day after that is the previous day +1. The row above picks the day cell and displays the day of the week. All of the rows pick from the current day and have a conditional format on a weekday formula to gray out the weekends. I have tried to reference the previous day and the start day with an IF formula to have it "" for the short months and +1 for the long months, but it does not seem to recognize the date format and return what I want. It was suggested that I try MONTH+1, and this did not seem to work either. Any ideas?????? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Timesheet help
I assume your pay periods are 1-15 and 15-last?
In any case, try this. Assuming that cell A2 has your start of pay period date, such as 4/16/08. Then out where you might have 29, 30 and/or 31 (must not forget February) try a formula such as this in the 3rd from last column (which in my test was column N, with base/first date of pay period in A2) so this formula went into N2 =IF(ISERR(DAY(M2+1)),"",IF(DAY(M2+1)<DAY($A2),"",M 2+1)) you can adapt that for the row above the numeric date row to display/not display the day of the week. Hope that helps some. "DaddyO" wrote: I am using 2003, and I am trying to fix my employers timesheet. It started as a basic timesheet where we had to manually fill in each date and shade in the weekend columns. I have everything worked out so that I can enter the start date and everything updates from there. My only problem is the months that have 31 days. I want to have the top two rows empty for that column if the month is not 31 days. It is currently set up so the first day of the pay period references the start of payperiod cell, then each day after that is the previous day +1. The row above picks the day cell and displays the day of the week. All of the rows pick from the current day and have a conditional format on a weekday formula to gray out the weekends. I have tried to reference the previous day and the start day with an IF formula to have it "" for the short months and +1 for the long months, but it does not seem to recognize the date format and return what I want. It was suggested that I try MONTH+1, and this did not seem to work either. Any ideas?????? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Timesheet help
You were absolutely correct with the twice monthly deal. The formula you
provided worked out beautifully, and it works like a charm. I am currious how that formula works for future reference. "JLatham" wrote: I assume your pay periods are 1-15 and 15-last? In any case, try this. Assuming that cell A2 has your start of pay period date, such as 4/16/08. Then out where you might have 29, 30 and/or 31 (must not forget February) try a formula such as this in the 3rd from last column (which in my test was column N, with base/first date of pay period in A2) so this formula went into N2 =IF(ISERR(DAY(M2+1)),"",IF(DAY(M2+1)<DAY($A2),"",M 2+1)) you can adapt that for the row above the numeric date row to display/not display the day of the week. Hope that helps some. "DaddyO" wrote: I am using 2003, and I am trying to fix my employers timesheet. It started as a basic timesheet where we had to manually fill in each date and shade in the weekend columns. I have everything worked out so that I can enter the start date and everything updates from there. My only problem is the months that have 31 days. I want to have the top two rows empty for that column if the month is not 31 days. It is currently set up so the first day of the pay period references the start of payperiod cell, then each day after that is the previous day +1. The row above picks the day cell and displays the day of the week. All of the rows pick from the current day and have a conditional format on a weekday formula to gray out the weekends. I have tried to reference the previous day and the start day with an IF formula to have it "" for the short months and +1 for the long months, but it does not seem to recognize the date format and return what I want. It was suggested that I try MONTH+1, and this did not seem to work either. Any ideas?????? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Timesheet help
The IF(ISERR( portion handles the situation where more than one of the last 3
days is in the next month. When that occurs, the first not-in-current-month date gets set to "" and so the math for calculating the next day/date fails with a #VALUE error. The ISERR test prevents that from interfering with downstream calculations. The IF DAY(newlycalculated) < DAY(startdate) test simply relies on the fact that at the end of a month you're going to have large numbers such as 28, 29, 30 or 31, while at the start of that period you're going to have medium sized numbers like 15 or 16, so if you roll over to a new month, the day will be a small number, as 1 or 2 or 3. "DaddyO" wrote: You were absolutely correct with the twice monthly deal. The formula you provided worked out beautifully, and it works like a charm. I am currious how that formula works for future reference. "JLatham" wrote: I assume your pay periods are 1-15 and 15-last? In any case, try this. Assuming that cell A2 has your start of pay period date, such as 4/16/08. Then out where you might have 29, 30 and/or 31 (must not forget February) try a formula such as this in the 3rd from last column (which in my test was column N, with base/first date of pay period in A2) so this formula went into N2 =IF(ISERR(DAY(M2+1)),"",IF(DAY(M2+1)<DAY($A2),"",M 2+1)) you can adapt that for the row above the numeric date row to display/not display the day of the week. Hope that helps some. "DaddyO" wrote: I am using 2003, and I am trying to fix my employers timesheet. It started as a basic timesheet where we had to manually fill in each date and shade in the weekend columns. I have everything worked out so that I can enter the start date and everything updates from there. My only problem is the months that have 31 days. I want to have the top two rows empty for that column if the month is not 31 days. It is currently set up so the first day of the pay period references the start of payperiod cell, then each day after that is the previous day +1. The row above picks the day cell and displays the day of the week. All of the rows pick from the current day and have a conditional format on a weekday formula to gray out the weekends. I have tried to reference the previous day and the start day with an IF formula to have it "" for the short months and +1 for the long months, but it does not seem to recognize the date format and return what I want. It was suggested that I try MONTH+1, and this did not seem to work either. Any ideas?????? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Timesheet | Excel Worksheet Functions | |||
Timesheet | Excel Worksheet Functions | |||
Timesheet help | Excel Worksheet Functions | |||
Timesheet | Excel Worksheet Functions | |||
timesheet | Excel Worksheet Functions |