Need help with date and time formula

[DanielBMe]

Hello,

I am having a really difficult time trying to figure out a formula for the following.

I have a Received Date as 2014-10-01 16:04:43. My works business hours are 8am to 6pm Mon to Fri. If the receive time is after 6pm I would like the date to show as the next day with a time of 8am. If the time is prior to 8am, so between midnight and 7:59am I'd like it to show as 8am same day.
So
2014-10-01 19:04:43 would be 2014-10-02 08:00:00 and
2014-10-01 06:04:43 would be 2014-10-01 08:00:00

To top if off if it's Friday after 6pm, the date should roll to the Monday so

2014-11-14 19:04:43 would be 2014-11-17 19:04:43

I figured out the followwing for the time =IF(HOUR(B2)>18,"08:00:00 AM",IF(HOUR(B2)<8,"08:00:00 AM",B2)) but it then involves adding in multiple columns with multiple other formulas.

I was curious if there's a way to have it all in one formula or at most two?

Any help would be appreciated!

[ChemistB]

Try this formula for "Corrected Time column"

=IF(AND(WEEKDAY(B2,1)=6, HOUR(B2)>18), INT(B2)+3+"8:00", IF(HOUR(B2)<8, INT(B2)+"8:00", IF(HOUR(B2)>18, INT(B2)+1+"8:00",B2)))

This assumes you never receive deliveries on Saturday or Sunday

[DanielBMe]

That worked for the time for Mon to Fri. Still have a problem for the weekends though.

[ChemistB]

So you do get packages received on Saturday and Sunday?

=IF(WEEKDAY(B2,2)>5, WORKDAY(B2,1)+"8:00", IF(AND(WEEKDAY(B2,2)=5, MOD(B2,1)>="18:00"+0), INT(B2)+3+"8:00", IF(HOUR(B2)<8, INT(B2)+"8:00", IF(MOD(B2,1)>"18:00"+0, INT(B2)+1+"8:00",B2))))

I replaced HOUR(B2)>18 with MOD(B2,1)>"18:00"+0 because 6:30 PM would show as 18 for example and not go to the next day.

[DanielBMe]

That worked perfectly! Much appreciated!

[DanielBMe]

Actually just thought I'd show the formula that ended up working better for me. I have two dates a received date and a ticket creation date. Problem is tickets can be received after hours and on weekends etc. I needed something that could work out the working hours.
B6 = Received Date
C6 = Ticket created date
M6 = Hour of last business day (in my case we are open until 6pm)
L6 = Hour of start time for business day (in my case we open at 8am
n6= holiday

=IF(AND(INT(B6)=INT(C6),NOT(ISNA(MATCH(INT(B6),HolidayList,0)))),0,ABS(IF(INT(B6)=INT(C6),ROUND(24*(C6-B6),2),
(24*(M6-L6)*
(MAX(NETWORKDAYS(B6+1,C6-1,HolidayList),0)+
INT(24*(((C6-INT(C6))-
(B6-INT(B6)))+(M6-L6))/(24*(M6-L6))))+
MOD(ROUND(((24*(C6-INT(C6)))-24*L6)+
(24*M6-(24*(B6-INT(B6)))),2),
ROUND((24*(M6-L6)),2))))))

[daddylonglegs]

=IF(AND(INT(B6)=INT(C6),NOT(ISNA(MATCH(INT(B6),HolidayList,0)))),0,ABS(IF(INT(B6)=INT(C6),ROUND(24*(C6-B6),2),
(24*(M6-L6)*
(MAX(NETWORKDAYS(B6+1,C6-1,HolidayList),0)+
INT(24*(((C6-INT(C6))-
(B6-INT(B6)))+(M6-L6))/(24*(M6-L6))))+
MOD(ROUND(((24*(C6-INT(C6)))-24*L6)+
(24*M6-(24*(B6-INT(B6)))),2),
ROUND((24*(M6-L6)),2))))))

That formula doesn't work if B6 or C6 are outside working hours, e.g. for B6 as Sat 22-nov 2014 at 14:00 and C6 as Mon 24-nov 2014 at 14:00 I expect you want the answer 6 because there are only 6 working hours between those two date/time values - the above formula gives 10

This formula will return 6 as expected and gives correct results for any values of B6 and C6 assuming B6 < C6

=24*((NETWORKDAYS(B6,C6,HolidayList)-1)*(M6-L6)+IF(NETWORKDAYS(C6,C6,HolidayList),MEDIAN(MOD(C6,1),M6,L6),M6)-MEDIAN(NETWORKDAYS(B6,B6,HolidayList)*MOD(B6,1),L6,M6))

format result cell as number