How to automatically chart 1 and 0's for roster times of employees in hourly overview

Dear Excel forum,

I've been browsing around for a solution but could't find one yet.

I'm struggling with attached sheet where I've put 1's when an employee is working for the given roster times between the hours 06:00 and 05:00 (operating hours) by hand.

Since this is alot of work and will have to do this for around 100 lines per day for 4 different weeks, an automatic way would really help me out.

Im using a Office365 proplus version of Excel on windows 10.

I really appreciate any help on this, hope somebody knows how to do this with a formula.

Best regards, Christiaan

Hello and welcome to the forum.

Try this in K2:

=IF(AND(K$1>=$F2,K$1<=$F2+$H2),1,"")

Drag the formula to the right and down.

Hi Pepe,

I am so sorry for doing so and well noted. Attachment is in this thread, as I posted yesterday. Making the formula work in the sheet and upload the working sheet in this thread would be of great help to me and would save a ton of work.

Thank you so much for your help,

Here is your workbook from post #1 with the formula from post #2 entered in.

Maybe your regional settings require a different spelling of the IF or AND functions or ; instead of ,.

Whatever it is, it should be translated automatically upon opening this workbook.

Thank you so much! I had tried regional settings wich would't help, now I see that part of the formula $F2,K$1 as you wrote before had to be $F2;K$1.

Thanks again so much, you saved me alot of work so weekend can start early

Cheers, have a good weekend!

Great, happy to help.

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