Active Member
November 29, 2016
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.
January 18, 2015
See if the following gives you the solution:
http://www.mrexcel.com/forum/e.....-date.html
You may also find the following of interest:
Active Member
November 29, 2016
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.
VIP
Trusted Members
June 25, 2016
VIP
April 21, 2015
VIP
Trusted Members
June 25, 2016
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
1 Guest(s)