4 working hours before a date and time

Hello!

I'm trying to work out a formula that will tell me what time it was 4 working hours ago, I've asked ChatGpt (I know, sorry) for some help and it is only giving me formula's that will tell me 4 hours (not working hours) and it doesn't seem to understand what working hours are (at least I'm not out of a job...) ANYWAY

The date and time it needs to look at is in cell AC1 and it is in a dd/mm/yyyy hh:ss format. The formula is needed so I can work out whether an email is within our 4 working hour turn around time.
Working hours are
Monday - Friday: 9am - 6:30pm
Saturday: 9am - 4pm
Sunday: Closed

It keeps giving me this formula =IF(OR(WEEKDAY(AC1,2)>5,HOUR(AC1)<9,HOUR(AC1)+MINUTE(AC1)/60>=18.5), WORKDAY(AC1,-1)+TIME(18,30,0), IF(HOUR(AC1)-4/24<9, WORKDAY(AC1,-1)+TIME(18,30,0), AC1-TIME(4,0,0))) but say the date and time displayed was 11/01/2024 07:16 (using the =NOW() formula) that returns 18:30 which is not right. All others just give the complete wrong date and time.

Between 10/01/2024 18:30 to 11/01/2024 09:00 the 4 working hour timer should essentially stop because that is outside of working hours.

Administrative Note:

Welcome to the forum.

Is your forum profile showing the Excel PRODUCT that you need this to work for?

Members will tailor the solutions they offer to the Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your product is for Mac, please also state this.

The three most recent Excel products are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the version number in your profile (e.g. MS365 Version 2306). This is in the About Excel section further down the Account page.

Thanks.

Please provide a sample workbook with a few manually calculated examples.

Done - don't know how to add it to another post,

Column A = the dates and times emails have been received
C2 = would normally show the =NOW() formula, but just so the time doesn't keep changing have fixed this to 7:30am
D2 = would normally show the working hours formula, but I've changed this to a fixed time so that the conditional formatting works
E2 = wouldn't normally be there - that's just putting the formula in there for now so it can be seen how it's wrong

Finding a solution to a problem like this usually requires many examples that cover the range of possible scenarios.
For the one example given the formula could be:

Formula:

Please Login or Register  to view this content.

Note that K2 and K3 contain the start and end times for weekdays.
Let us know if you have any questions.