Maximizing Leave allocation hours

[bearygump]

Hello! I would like to know if there is a way to allocate a certain number (from a single cell) to a set of cells depending on the constraints.

Setting the scene: i have allocated leave per year (values in hours). i already have an excel that calculates my remaining leave. what i want to do now is to allocate the remaining leave into various cells. Conditions are: Mondays to Thursdays is considered to be 8.5 hours of leave, but Friday is 8 hours of leave. (Saturdays and Sundays do not affect the count, so they are 0 hours of leave). I would like the formula to be able to tell me how many 8.5 hour leave i can take, or how many 8 hour leave i can take, or a combination of 8.5 and 8 hour leave that i can take. (and any remainder if possible).

 

[Logit]

Paste this macro into the Worksheet module for Sheet 2019 :

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Range("I3").Value = Range("E3").Value / 8.5
    Range("I4").Value = Range("E4").Value / 8
End Sub

There is one formula to be inserted in I5 : =SUM(I3:I4)

 

[bearygump]

Paste this macro into the Worksheet module for Sheet 2019 :

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Range("I3").Value = Range("E3").Value / 8.5
    Range("I4").Value = Range("E4").Value / 8
End Sub

There is one formula to be inserted in I5 : =SUM(I3:I4)

Hello Logit!

This works perfectly fine. However, is there a way to make the result show only whole numbers, and the remaining "unused" hours from I3 and I4 to show in J3 and J4. (see file attached)

Explanation of image:
Remaining hours = 60.5
8.5 hour allocation = 7.11... (but I need this to show "7" only, regardless of decimal point)
8.5 * 7 = 59.5
60.5 - 59.5 = 1 <-- this value should be shown in J3


Also, for the combination, I was hoping it can tell me that I can use like three 8 hour leaves, and two 8.5 hour leaves (or all other possible combinations), based on the remaining hours in E3. like a way to maximize the leaves if i want to use both 8.5 and 8hours. instead of just all 8.5 hours (as shown in I3) or just all 8 hours (I4).

to be more clear. let's say value of E3 = 49.
Result in I5 (or a series of cells) will be:
4 8hour leave, 2 8.5hour leave (4*8)+(2*8.5) = 49
1 8hour leave, 4 8.5hour leave, remainder 7hours (1*8)+(4*8.5)+7 = 49

 

[Hui]

1 week of leave is 4x8.5+8 = 42 Hrs
You have 69 hours so that is 1 week and 27 Hrs left over
27 Hrs is 3 days x 8.5 = 25.5
plus 1.5 hrs left over

So 69 Hrs is 7 days x 8.5 hrs + 1 day x 8 hrs
with an accumulation of 1.5 hrs leave

The actual issue is when you start as if you start on a Thursday you will use 2 Fridays instead of 1