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?