Help with formula for hours and days

[rosscortb]

Hello,

I have a holiday spreadsheet.

We are entitled to 200 hours annual leave overall which works out as 25days(8hr days)(Mon-Thursday). But, if you only took Fridays then you could have 40 days as one Friday annual leave is only 5hrs leave and not the 8hrs.

We are also allowed to take 2hrs off if we wish.

I am looking for help with a formula that works out how much days and hours I have left available to take.

My spreadsheet allows me to enter in the start date, end date which works out the number of days(but i need to disregard weekend days) and I can add in how many hours. So,

For example.
Start Date End Date Reason Number of Hrs Number of Days
23 May 2017 25 May 2017 Wedding 8.0(maybe should read 24?) 3

Subtracting the hours is the easy part but not sure how I get it to display that I have not actually taken a full day(overall) if it is less than 8hrs?
Also, it probably should display half days which would be 4hrs.

Hope fully I have attached the spreadsheet correctly.

Thanks

Ross

[davsth]

try networkdays as a function for the number of days.

The number of days isn't really relevant it you work in hours. You could divide by 8 or (37/5) , or just sum the number of days if you wanted number of days where some leave was taken

hours should be total hrs ideally

[JohnTopley]

Number of Working Days:

=NETWORKDAYS.INTL($A2,$B2)

You can add Holidays to this if required.

Number of Fridays

=SUMPRODUCT(N(WEEKDAY(ROW(INDIRECT($A2&":"&$B2)))=6))

For Half days you either simply manually input the hours of code ("F"=Full, "H"=Half)

[davsth]

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)<5))*8+SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)=5))*5 may work out the number of hours although you would need to overtype for half days