I've been reading up on Excel's date and time functions and can't really figure out the best way of doing this. Any input would be appreciated.
Currently I'm using Excel 2007
(Start Date/time) A1 = 11/25/2016 14:00
(Hour to fix) B2 = 20 hours
(End date/time) C1 = End time?
(Business hour = Mon - Sun 08:00 - 20:00)
I'd like to enter a date and time into a cell (Start Date/time) and have another cell return the date and time that the machine should be done with the task including weekends (End Date/time). This would be based on a certain number of "business hours" that would be calculated in another cell.
See if the following gives you the solution:
http://www.mrexcel.com/forum/excel-questions/131864-adding-business-hours-date.html
You may also find the following of interest:
http://www.cpearson.com/excel/check-if-value-is-between-certain-times.htm
Hi Derek Brown,
Thanks for reply. formula given is almost similar with what I'm currently used.
=WORKDAY(A1,CEILING((B2+MOD(A1,1)-P6)/(R6-P6),1)-1)+MOD(A1,1)+B2-CEILING(MOD(A1,1)+B2-P6,R6-P6)+R6-P6
P6 = 8:00 AM
R6 = 20:00 PM
It still not calculate Saturday & Sunday due to Business hour is Monday to Sunday. When I see formula of "Workday(start_date,days,[holidays])" I know that it's only available from Monday to Friday. I also found formula workday.int(start_date,[weekend],[holidays]) but it's only function for excel 2010.
Any input that can include weekend would be appreciated.
Hi elf
If you can post some examples (the more examples the better) of what you enter and their expected results in an attachment, it would be easier for others to understand what you want.
Sunny
Ok please refer to attachment.
I want to find formula for fix time in column (H12,I12,J12,K12,L12 & M12). The result should be appear once I insert date & time in row 7 & 8.
Most of formula I found for specific time only work for Monday to Friday. I'm using Excel 2007.
Tricky one, also because of the exceptions. Was thinking of the 'day' function works here also? I try some things out, but that'll take some more time.
Hi elf
I will give this question a shot although it is a tough one for me.
It will ONLY work with the following criteria:
1) Business day Monday-Sunday (including public holidays etc)
2) Business hour is from 0800 to 2000.
3) Hours To Fix as 7.5, 14 and 20 hours.
Hope this will be a starting point for other solutions/suggestions.
Sunny
