Hi all:
I am trying calculate number of hours in C5 from two dates with time (A5 = Start date: 04/09/2014 12:00:00 PM & B5 = End date: 05/09/2014 16:00:00 PM).
Requirements:
Regular work hours are 9:00 am to 4:00pm( 9:00 to 16:00 hours)
It should exclude start hour and end hour (only calculate hours between hours mentioned in dates ( e.g. Sep 4, 2014 12:00PM & Sep 5, 2014 16:00, it should count between 12:00 & 16:00 and give me 3 hours, NOT 4 hours.).
It should exclude weekends (sat & sun)
It should exclude stat holidays. I have list of stat holidays in sheet Holidays in column A
It should consider half days work hours which are listed in sheet Holidays column F & G (9:00 to 13:00). I will keep this list in case we change these hours. And Half work days dates are listed in sheet Holidays, column F. I will keep adding/change these dates because every year dates changes like around christmas or so. Basically list will grow like stat holidays list.
I have formula in C5 (copied from elsewhere) but unable to manage it. How to exclude stat holidays and half work days.
2. Second formula I have in F11, read last date/max date from colum B5:B45 (e.g. last date: Nov 20, 14 12:00:00 PM). Then add number of hours from E11 and give me future date and time.
Requirements:
Regular work hours are 9:00 am to 4:00pm( 9:00 to 16:00 hours)
It should exclude weekends (sat & sun)
It should exclude stat holidays. I have list of stat holidays in sheet Holidays in column A
It should consider half days work hours which are listed in sheet Holidays column F & G (9:00 to 13:00). I will keep this list in case we change these hours. And Half work days dates are listed in sheet Holidays, column F. I will keep adding/change these dates because every year dates changes like around christmas or so. Basically list will grow like stat holidays list.
My formula is working now but unable to handle stat holidays and half work day dates.
I do not know if these formulas can be done just in excel or we need vba code. I spent enough time to figure out the solution and burn my mind. Finally decided to ask gurus.
Hopefully, someone will help.
Attached is sample worksheet. Using excel 2003.
Thanks
Ria
I am trying calculate number of hours in C5 from two dates with time (A5 = Start date: 04/09/2014 12:00:00 PM & B5 = End date: 05/09/2014 16:00:00 PM).
Requirements:
Regular work hours are 9:00 am to 4:00pm( 9:00 to 16:00 hours)
It should exclude start hour and end hour (only calculate hours between hours mentioned in dates ( e.g. Sep 4, 2014 12:00PM & Sep 5, 2014 16:00, it should count between 12:00 & 16:00 and give me 3 hours, NOT 4 hours.).
It should exclude weekends (sat & sun)
It should exclude stat holidays. I have list of stat holidays in sheet Holidays in column A
It should consider half days work hours which are listed in sheet Holidays column F & G (9:00 to 13:00). I will keep this list in case we change these hours. And Half work days dates are listed in sheet Holidays, column F. I will keep adding/change these dates because every year dates changes like around christmas or so. Basically list will grow like stat holidays list.
I have formula in C5 (copied from elsewhere) but unable to manage it. How to exclude stat holidays and half work days.
2. Second formula I have in F11, read last date/max date from colum B5:B45 (e.g. last date: Nov 20, 14 12:00:00 PM). Then add number of hours from E11 and give me future date and time.
Requirements:
Regular work hours are 9:00 am to 4:00pm( 9:00 to 16:00 hours)
It should exclude weekends (sat & sun)
It should exclude stat holidays. I have list of stat holidays in sheet Holidays in column A
It should consider half days work hours which are listed in sheet Holidays column F & G (9:00 to 13:00). I will keep this list in case we change these hours. And Half work days dates are listed in sheet Holidays, column F. I will keep adding/change these dates because every year dates changes like around christmas or so. Basically list will grow like stat holidays list.
My formula is working now but unable to handle stat holidays and half work day dates.
I do not know if these formulas can be done just in excel or we need vba code. I spent enough time to figure out the solution and burn my mind. Finally decided to ask gurus.
Hopefully, someone will help.
Attached is sample worksheet. Using excel 2003.
Thanks
Ria
