Calculation of a certain number of hours out of 24 over multiple days

[Bernice75]

I am looking to place a formula in cell D1 that will show 14 paid hours per 24 hour period minus 2 hours from the start. In cell E1 I need the same only 6 hours from start.

For example. In the attached spreadsheet in cell A1 is 4/10 13:00. In cell B1 is 4/12 18:00. In cell C1 is =(B1-A1)*24. I need cell D1 to equal 31 and cell E1 to equal 28.

For D1, 2 hours are free which takes the start time for the calculation to 4/10 15:00. 14 hours out of every 24 hour period is billed. (4/10 15:00 to 4/11 15:00=14, 4/11 15:00 to 4/12 15:00=14, 4/12 15:00 to 4/12 18:00=3 for a total of 31 hours invoiced.

For E1, 6 hours are free which takes the start time for the calculation to 4/10 19:00. 14 hours out of every 24 hour period is paid. (4/10 19:00 to 4/11 19:00=14, 4/11 19:00 to 4/12 18:00=14 for a total of 28 hours billed)

Is there a formula or macro I can use to do this calculation?

[BoredWorker]

Hi,

Did E1 should be 27 instead?

if yes,
you may apply this two formula for a meanwhile,
as it may have formula that more simply

i use mod to get the hrs that no enough one day, and add in days that multiple with 14.

In D1,

Please Login or Register  to view this content.

In E1,

Please Login or Register  to view this content.

[Bernice75]

No, it should be 28 hours. The first 6 hours are free. That would change the beginning time of the calculation to 19:00. We then pay 14 hours out of every 24 hours period, which would be 28 hours in this case.

I also apologize for lack of a spreadsheet attachment. It would not allow me to attach the sheet.

[Bernice75]

Thank you so much for the first formula, so far with my testing it is working perfectly for what I need it to do!! I'll be testing it even more extensively tomorrow.

Hi,

Did E1 should be 27 instead?

if yes,
you may apply this two formula for a meanwhile,
as it may have formula that more simply

i use mod to get the hrs that no enough one day, and add in days that multiple with 14.

In D1,

Please Login or Register  to view this content.

In E1,

Please Login or Register  to view this content.

[bebo021999]

There are other cases of duration, i.e 4/10 1:00:00 PM to 6/10 11:00:00 AM (end time is smaller than start time)
Upload a sample WS with more expected results.
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Bernice75]

It is not something that the process shows up on an excel sheet. Right now we are manually calculating all of these results manually and inputting the resulting number on the spreadsheet. I tried to upload my sample workbook earlier, but it told me the page does not exist when I clicked on upload. I do not have a "go advanced" anywhere on this page.

[BoredWorker]

one question to ask,

how the hours is count if the day is more 14 hours but less the 24 hours.

[Bernice75]

I'm not sure if this is going to work.

[Bernice75]

if the day is longer than 14 and less than 24 we pay 14 hours.

[BoredWorker]

try

D1

Please Login or Register  to view this content.

E1

Please Login or Register  to view this content.

[bebo021999]

Try:
For 2 free hours:

Please Login or Register  to view this content.

Change 2 to 6