#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,886
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Annual Leave Aussie Peter Excel Discussion (Misc queries) 1 January 31st 06 04:25 AM
I need a template for Employee annual leave tracking Pamela Aranguiz Excel Discussion (Misc queries) 1 January 12th 06 02:35 AM
Annual leave calender year 2006 - template Mohamed Excel Discussion (Misc queries) 1 November 29th 05 05:56 PM
Tracking annual leave opos New Users to Excel 4 August 15th 05 11:09 AM
annual leave planner for approx 100 staff members wally Excel Discussion (Misc queries) 1 March 22nd 05 12:04 PM


All times are GMT +1. The time now is 11:43 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"