Count Base Hours for Attendance Report

[naveron]

Hi Excel experts!

I have come once again to seek for your invaluable help.

I'm in need to count for base work hours for my colleagues attendance record. The thing is, our work space uses shift as the basis for work hours count. However, some days are different with another days. To make it clearer, here is the schedule for our work space:

Monday – Thursday
1st Shift 09:00 – 15:30 = 6.5 hours
Mid Shift 12:00 – 18:00 = 6 hours
2nd Shift 15:00 – 20:30 = 5 hours

Friday – Sunday
1st Shift 09:00 – 15:30 = 6.5 hours
Mid Shift 13:00 – 19:00 = 6 hours
2nd Shift 15:00 – 21:30 = 6.5 hours

I use 24 hours format, because that's the format we use here.

I have attached a sample file where I think what I need help the most is in the Base Hours column. I have put some effort to cover the conditions, but I haven't figured out how to include several criteria to determine the base hours.
When formulas & functions are getting longer, I get so much confused. So I come here to seek for help in that matter. Lol.

I hope someone has time to help me in this. Thank you very much!

[davsth]

Its not entirely clear what you are asking but putting the days in as date makes things easier. A lookup of some sort to get the time makes it a bit more generic

perhaps g4 =MIN(IF(OR(B4="Minggu",B4="Senin",B4="Selasa"),VLOOKUP(E4,$J$6:$L$8,3,FALSE),VLOOKUP(E4,$J$11:$L$13,3,FALSE)),MROUND(F4*24,0.5))

[naveron]

Sorry, I thought the day's name would change to English name when opened in Laptop/PC with Western regional settings.

But just fyi
Minggu = Sunday
Senin = Monday
Selasa = Tuesday
Rabu = Wednesday
Kamis = Thursday
Jumat = Friday
Sabtu = Saturday

Ok, I uploaded a file with better explanation. I hope.

[davsth]

=MIN(IF(OR(B4="Minggu",B4="Sabtu",B4="Jumat"),VLOOKUP(E4,$J$6:$L$8,3,FALSE),VLOOKUP(E4,$J$11:$L$13,3,FALSE)),MROUND(F4*24,0.5))

the days were text, if they had been dates formatted at the day eg =today() formatted as a custom, format of DDDD will give Friday as it will adjust to the region

[naveron]

Ah, I see. I've fixed it.

[naveron]

=MIN(IF(OR(B4="Minggu",B4="Sabtu",B4="Jumat"),VLOOKUP(E4,$J$6:$L$8,3,FALSE),VLOOKUP(E4,$J$11:$L$13,3,FALSE)),MROUND(F4*24,0.5))

the days were text, if they had been dates formatted at the day eg =today() formatted as a custom, format of DDDD will give Friday as it will adjust to the region

But now that I've changed the day's name using date formatted as DDDD, how should I input the day's name into the formula? Because I used the formula you provided, it doesn't seem to work, because now it showed up as #NAME?

Thank you, to have taking the time to help.

Edit:
Ah, no matter. It seems to work now, I don't know why it shows up as #NAME? before. Maybe I missed something. But thank you very much for your help!