PDA

View Full Version : Excel formula help to check type of leave and calculating total days



mwvirk
05-20-2013, 12:50 AM
please help to solve the issue (attached)

i am creating a leave request form. if type of leave is "Annual" or "TIL (time in lieu)" then it should calculate working days only which are from Sunday to Thursday but should add public holidays (range mentioned in the sheet)

anything other than annual leave or TIL, it should calculate calendar days. there are around 8 types of leaves other than Annual & TIL

i am using this formula but it's failing:
=IF(OR($C$16="",$E$16="",$G$16=""),"",IF($B$16=$O$53:$O$54,SUMPRODUCT((WEEKDAY(ROW(INDIRECT(C16&":"&E16)))<6)*ISNA(MATCH(ROW(INDIRECT(C16&":"&E16)),$P$65:$P$77,0)))))

please note that different users are using this leave form. some of them will be having office 2007. therefore; can't use office 2010 or 2013 functions. like networkdays.intl etc

additionally if you can help to check:
-end date and resume date are not less than start date.
-don't accept entry for end date and resume date if start date is blank.
-don't accept resume date of end date and start date are blank.

thank you.

Teeroy
05-20-2013, 05:04 AM
I have not looked at your example but my first thought would be to use the Networkdays function for this (in the Analysis ToolPak Add-In) which has been available since 2003. Since your week is Sunday to Thursday give all dates a +1 offset to use the inherent work week of Monday - Friday of Networkdays.

SamT
05-20-2013, 10:47 AM
I used to use Spreadsheet forms like you. When Formulas got too unwieldy, I moved them outside the Form, broke them into bits and put a cell reference on the Form to the final result. :banghead:

Then I discovered VBA UserForms:biggrin:

Sooo much better