Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting no of days from a list of running dates for a 12 month pe
Hi I am trying to set up a sick leave list which will work out the cumulative
total for the last 12 months (from last day of last sick entry). The problem I have as the ist is added to I need a function/formula to work out 12 months from last date and then add up the no. of sick days from then to last date. Below is the chart Column 1 Start date of sickness column 2 End date Column 3 the number of days sick in period (column 2 less column 1 in days) column 4 the number of days sick in period above excluding week ends (column 2 less column 1 less weekends in days) column 5 total sick days in last 12 months from date in column 2 going back a year including weekends column 6 total sick days in last 12 months from date in column 2 going back a year excluding weekends What function or formulas can I use to calculate column 3,4,5 & 6. Im a not very experienecd in Excel so any help appreciated and make it easy as I struggle sometimes. Thanks Bill |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting no of days from a list of running dates for a 12 month pe
"bill999" wrote in message ... Hi I am trying to set up a sick leave list which will work out the cumulative total for the last 12 months (from last day of last sick entry). The problem I have as the ist is added to I need a function/formula to work out 12 months from last date and then add up the no. of sick days from then to last date. Below is the chart Column 1 Start date of sickness column 2 End date Column 3 the number of days sick in period (column 2 less column 1 in days) column 4 the number of days sick in period above excluding week ends (column 2 less column 1 less weekends in days) column 5 total sick days in last 12 months from date in column 2 going back a year including weekends column 6 total sick days in last 12 months from date in column 2 going back a year excluding weekends What function or formulas can I use to calculate column 3,4,5 & 6. 3 - =B2-A2 and fornat as general 4 - =NETWORKDAYS(A2,B2) 5 - =IF(B4=DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())),B4-MAX(A4,DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))-1),0) 6 - =IF(B4=DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())), NETWORKDAYS(MAX(A4,DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))),B4),0) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting no of days from a list of running dates for a 12 month pe
Hi,
The formula below looks at the dates in column B and adds up the instances of sickness in column E that occur within the last rolling 12 months. I think you will find it fairly easy to understand and modify for other columns. =SUMIF(B1:B1000,"<="&DATE(YEAR(TODAY()), MONTH(TODAY()) - 12, 1),E1:E1000) Mike Mike "bill999" wrote: Hi I am trying to set up a sick leave list which will work out the cumulative total for the last 12 months (from last day of last sick entry). The problem I have as the ist is added to I need a function/formula to work out 12 months from last date and then add up the no. of sick days from then to last date. Below is the chart Column 1 Start date of sickness column 2 End date Column 3 the number of days sick in period (column 2 less column 1 in days) column 4 the number of days sick in period above excluding week ends (column 2 less column 1 less weekends in days) column 5 total sick days in last 12 months from date in column 2 going back a year including weekends column 6 total sick days in last 12 months from date in column 2 going back a year excluding weekends What function or formulas can I use to calculate column 3,4,5 & 6. Im a not very experienecd in Excel so any help appreciated and make it easy as I struggle sometimes. Thanks Bill |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Blanks in a list of dates by month | Excel Worksheet Functions | |||
Counting days in month | Excel Discussion (Misc queries) | |||
Counting the days worked in a month by employee | Excel Worksheet Functions | |||
Counting te days worked in a month | Excel Worksheet Functions | |||
Calculate running target by days in the month | Excel Worksheet Functions |