I had previously shared about some of the formula that I used for calculating the working hours using Excel. I realised that since my lunch hours are fixed, I wanted to let them repeat for the month excluding the Public Holidays, weekends and days when I am not at work. So, I updated my formula for lunch time.
=IF(OR(WEEKDAY(A3,2)>5,I3<>””),””,Lunch!A$2)
My I3 is the Remarks column that will state if I am on leave or there is a public holiday. A3 checks that the date is a weekday or not. The 2 in Weekday Formula is to state that my week starts on Monday instead of Sunday.
Then, I copied and pasted the formula for the rest of days of the week. Works like a charm. I no longer need to key in my lunch time in Excel.
