Office Timing - Nested if formula

[Manish Kumawat]

Dear Excel Masters,

Greetings of the day!!!!

I have uploaded a sheet where you will find timings according to week days. I am stuck in nested if conditions, if I use multiple if conditions there is limit for the formula. Basis my requirement there is total 7 conditions Named:

1. No issue

2. Early Out

3. Late in & Early Out

4. No In Punch & Early Out

5. Late in & No Out Punch

6. No Punch

7. No In punch

The timing is from Monday to Friday would be 9:30:00 to 17:30:00 and for all Saturdays the time would be 9:30:00 to 13:30:00.

More details for the requirement I have mentioned in the formula.

Please help me to get desired result or suggest me some other formula for the same.


Thanks

Manish Kumawat

PS: I Have used trim function for cell C2 & D2 because the generated reports contains one blank space before the timings.

 

[vletm]

Something like this with two help columns...

 

[PCosta87]

Hi,

Used a helper table + INDEX/MATCH (array).
Please see attached

Hope this helps

 

[Manish Kumawat]

Hi,

Used a helper table + INDEX/MATCH (array).
Please see attached

Hope this helps

Hi, Thanks for the other way.

I have also used nested if, if you can edit the same that would be an added help.