Two different rates of payment for duty during certain times

[2021magic2021]

Cell a1 has the start time, cell b1 has the end time.
I am trying to create a formula that will calculate two types of payments, one applicable between the hours 6pm and 8pm, and the other applicable between the hours of 8pm and 8am the next day.

so in cell c1 i could have the number of hours for type 1 allowance, and in d1 the number of hours for type 2 allowance.

start times can be anytimes, and end times can go beyond midnight.

i would really appreciate any help, i think there are some other examples already solved, however i couldn't find one that differentiated between the hours

for example, duty between 7pm and 1am would mean 1 hour type 1 and 5 hours type 2.

thanking you,

kind regards,
Magic

[AliGW]

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

Administrative Note:

Members will tailor the solutions they offer to the version (NOT release number) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

[2021magic2021]

file uploaded now

[AliGW]

Are you still using Excel 2010?

[2021magic2021]

yes, i suppose, why?

[2021magic2021]

it actually says 2016 in the product information, no idea why it would say 2010

[AliGW]

Because your profile needs updating if you have upgraded from Excel 2010 to something newer. I mentioned this in my first post.

Thanks for the workbook.

[2021magic2021]

i've uploaded a sample, it should be up already, about 3 posts higher up. i will update the info in the profile, never realised that. apologies

[AliGW]

Yes, I am looking at the workbook - thanks.

[soledad]

Formula in C2 then copied down:

Please Login or Register  to view this content.

Apply the same formula to D2 by replace range {"6:00 PM","8:00 PM"} with {"8:00 PM","8:00 AM"}
Good luck

[2021magic2021]

@soledad

thank you so much, it worked for me. I did two changes to the formula though, one being replacing the PM/AM with the 24 hour format (6:00 PM replaced with 18:00:00) and in the end of the formula, i replaced 6:0 pm with the 24 hour format too, as the part hour was not coming up properly.

thank you again,

kind regards,

magic

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.