Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Annual Leave Planner
Hi I'm been trying to install a MS Excel spreadsheet 'Annual Leave Planner' , as created by http://www.meadinkent.co.uk/conditional-formatting.htm. I'm fairly well up on spreadsheets but can't get my head around the end part of the formula: and what it means plus if I insert the same name with another leave date booked, it inserts all the dates as soon as I type the name. The line of code that's gining me the problem is: SUMPRODUCT(($A5=SNames)*(SFrom<=F$4) * (STo=F$4)) + IF(WEEKDAY(F$4,2)5,2,0) Its the IF statement that I can't understand. I can get the cells to change by changing parameters (F$4,2) to say a 1, but I just don't understand the setup. Can anyone help please, please, pretty please, explain, as its becoming so frustrating AND I won't give up. Many thanks for any help. Alan Think the IF has just dawned on me but I'll leave it to you guys. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Annual Leave Planner
Hi Peter
Weekday(F4,2) will return a day number where Monday is 1 and Sunday is 7. If the result is greater than 5, i.e we are dealing with a Saturday or a Sunday, then add 2 days otherwise add nothing. -- Regards Roger Govier "peter" wrote in message ... Hi I'm been trying to install a MS Excel spreadsheet 'Annual Leave Planner' , as created by http://www.meadinkent.co.uk/conditional-formatting.htm. I'm fairly well up on spreadsheets but can't get my head around the end part of the formula: and what it means plus if I insert the same name with another leave date booked, it inserts all the dates as soon as I type the name. The line of code that's gining me the problem is: SUMPRODUCT(($A5=SNames)*(SFrom<=F$4) * (STo=F$4)) + IF(WEEKDAY(F$4,2)5,2,0) Its the IF statement that I can't understand. I can get the cells to change by changing parameters (F$4,2) to say a 1, but I just don't understand the setup. Can anyone help please, please, pretty please, explain, as its becoming so frustrating AND I won't give up. Many thanks for any help. Alan Think the IF has just dawned on me but I'll leave it to you guys. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Annual Leave | Excel Discussion (Misc queries) | |||
I need a template for Employee annual leave tracking | Excel Discussion (Misc queries) | |||
Annual leave calender year 2006 - template | Excel Discussion (Misc queries) | |||
Tracking annual leave | New Users to Excel | |||
annual leave planner for approx 100 staff members | Excel Discussion (Misc queries) |