LinkBack URL
About LinkBacks
Hi,
I need a formula to get No. of working days & Working days passed in a fortnight (Two fortnights indicated as 1 & 2).
Excel file attached with necessary information.
Pl. help.
Thanks,
Nagesh.
Hi,
I need a formula to get No. of working days & Working days passed in a fortnight (Two fortnights indicated as 1 & 2).
Excel file attached with necessary information.
Pl. help.
Thanks,
Nagesh.
Working days past in the fortnight (for the month)
Formula:
=IF(TODAY()>=DATE(YEAR(E2),MONTH(E2),LOOKUP(F2,{1,2},{1,16})),1+MEDIAN(DATE(YEAR(E2),MONTH(E2),LOOKUP(F2,{1,2},{1,16})),TODAY(),LOOKUP(F2,{1,2},DATE(YEAR(E2),MONTH(E2)+{0,1},{15,0})))-DATE(YEAR(E2),MONTH(E2),LOOKUP(F2,{1,2},{1,16}))-SUMPRODUCT((B$2:B$367<=MEDIAN(DATE(YEAR(E2),MONTH(E2),LOOKUP(F2,{1,2},{1,16})),TODAY(),LOOKUP(F2,{1,2},DATE(YEAR(E2),MONTH(E2)+{0,1},{15,0}))))*(B$2:B$367>=DATE(YEAR(E2),MONTH(E2),LOOKUP(F2,{1,2},{1,16})))))
Total Working days in the fortnight (for the month)
Formula:=1+LOOKUP(F2,{1,2},DATE(YEAR(E2),MONTH(E2)+{0,1},{15,0}))-DATE(YEAR(E2),MONTH(E2),LOOKUP(F2,{1,2},{1,16}))-SUMPRODUCT((B$2:B$367<=LOOKUP(F2,{1,2},DATE(YEAR(E2),MONTH(E2)+{0,1},{15,0})))*(B$2:B$367>=DATE(YEAR(E2),MONTH(E2),LOOKUP(F2,{1,2},{1,16}))))
Hope it helpfull
Another approach ....
G7: start date for 1st fortnight for given month
=E2
G8: start date for 2nd fortnight for given month
=INDEX($A$2:$A$367,MATCH(1,(MONTH($E$2)=MONTH($A$2:$A$367))*($C$2:$C$367=2),0))
Enter above with Ctrl+Shift+Enter
Replace 1 & 2 with F2 as required
in F7: working days in 1st fortnight
=NETWORKDAYS.INTL($G$7,$G$8-1,,Holidays)
in F8: working days in 2nd fortnight
=NETWORKDAYS.INTL($G$8,EOMONTH($E$2,0),,$B$2:$B$66)
F10: work days past (1st)
=NETWORKDAYS.INTL($G$7,TODAY()-1,,Holidays)
F11 (2nd)
=NETWORKDAYS.INTL($G$8,TODAY()-1,,Holidays)
Named range "Holidays" is b2:B66
Last edited by JohnTopley; 11-15-2016 at 04:44 AM.
Thank you very much Soledad. Your solution is working fantastic.
Thank you John Topley. While calculating as per your formula, NETWORKINGDAYS excluding Saturdays also, whereas we work on Saturday. Anyhow Thank you.
Best regards,
Nagesh.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks