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.
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.