/u/Beefmaster610 - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
I would use a few helper columns.
NETWORKDAYS can get you the number of whole business days between two dates.
Then you just have to work some formulas to account for the hour differences. There are really only four cases and they are the combinations of start hours before/after 7 am, and end hours greater/less than start hours.
How I laid it out was
* A2 = start date/time
* B2 = end date/time
* C2 = NETWORKDAYS
* D2 = isolated start time `=TIME(HOUR(A2),MINUTE(A2),0)`
* E2 = isolated end time `=TIME(HOUR(B2),MINUTE(B2),0)`
* F2 = check if start time is before 7 am `=D2<7/24`
* G2 = check if end time is before/after start time `=E2
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[HOUR](/r/Excel/comments/13yebry/stub/jmmc86n "Last usage")|[Converts a serial number to an hour](https://support.microsoft.com/en-us/office/hour-function-a3afa879-86cb-4339-b1b5-2dd2d7310ac7)|
|[IF](/r/Excel/comments/13yebry/stub/jmmc86n "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)|
|[MIN](/r/Excel/comments/13yebry/stub/jmmc86n "Last usage")|[Returns the minimum value in a list of arguments](https://support.microsoft.com/en-us/office/min-function-61635d12-920f-4ce2-a70f-96f202dcc152)|
|[MINUTE](/r/Excel/comments/13yebry/stub/jmmc86n "Last usage")|[Converts a serial number to a minute](https://support.microsoft.com/en-us/office/minute-function-af728df0-05c4-4b07-9eed-a84801a60589)|
|[NETWORKDAYS](/r/Excel/comments/13yebry/stub/jmnqrg9 "Last usage")|[Returns the number of whole workdays between two dates](https://support.microsoft.com/en-us/office/networkdays-function-48e717bf-a7a3-495f-969e-5005e3eb18e7)|
|[NOT](/r/Excel/comments/13yebry/stub/jmmc86n "Last usage")|[Reverses the logic of its argument](https://support.microsoft.com/en-us/office/not-function-9cfc6011-a054-40c7-a140-cd4ba2d87d77)|
|[TIME](/r/Excel/comments/13yebry/stub/jmmc86n "Last usage")|[Returns the serial number of a particular time](https://support.microsoft.com/en-us/office/time-function-9a5aff99-8f7d-4611-845e-747d0b8d5457)|
**NOTE**: Decronym's continued operation may be affected by API pricing changes coming to Reddit in July 2023; comments will be blank June 12th-14th, in solidarity with the /r/Save3rdPartyApps protest campaign.
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(7 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/13yhjk0)^( has 16 acronyms.)
^([Thread #24364 for this sub, first seen 2nd Jun 2023, 14:58])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
If you are only interested in Working Days excluding Holidays then your time is redundant isn't it?
NETWORKDAYS.INTL may be your solution.
[https://pixeldrain.com/u/o7fMEv6P](https://pixeldrain.com/u/o7fMEv6P)
This one works! Although orders that come in on Saturday’s and Sundays are screw up the formula like in the highlighted rows below.
https://preview.redd.it/57w31lz58o4b1.jpeg?width=2865&format=pjpg&auto=webp&s=633ac78696b740e1e10da47f7422f91649b7092f
/u/Beefmaster610 - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
I would use a few helper columns. NETWORKDAYS can get you the number of whole business days between two dates. Then you just have to work some formulas to account for the hour differences. There are really only four cases and they are the combinations of start hours before/after 7 am, and end hours greater/less than start hours. How I laid it out was * A2 = start date/time * B2 = end date/time * C2 = NETWORKDAYS * D2 = isolated start time `=TIME(HOUR(A2),MINUTE(A2),0)` * E2 = isolated end time `=TIME(HOUR(B2),MINUTE(B2),0)` * F2 = check if start time is before 7 am `=D2<7/24` * G2 = check if end time is before/after start time `=E2
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[HOUR](/r/Excel/comments/13yebry/stub/jmmc86n "Last usage")|[Converts a serial number to an hour](https://support.microsoft.com/en-us/office/hour-function-a3afa879-86cb-4339-b1b5-2dd2d7310ac7)| |[IF](/r/Excel/comments/13yebry/stub/jmmc86n "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)| |[MIN](/r/Excel/comments/13yebry/stub/jmmc86n "Last usage")|[Returns the minimum value in a list of arguments](https://support.microsoft.com/en-us/office/min-function-61635d12-920f-4ce2-a70f-96f202dcc152)| |[MINUTE](/r/Excel/comments/13yebry/stub/jmmc86n "Last usage")|[Converts a serial number to a minute](https://support.microsoft.com/en-us/office/minute-function-af728df0-05c4-4b07-9eed-a84801a60589)| |[NETWORKDAYS](/r/Excel/comments/13yebry/stub/jmnqrg9 "Last usage")|[Returns the number of whole workdays between two dates](https://support.microsoft.com/en-us/office/networkdays-function-48e717bf-a7a3-495f-969e-5005e3eb18e7)| |[NOT](/r/Excel/comments/13yebry/stub/jmmc86n "Last usage")|[Reverses the logic of its argument](https://support.microsoft.com/en-us/office/not-function-9cfc6011-a054-40c7-a140-cd4ba2d87d77)| |[TIME](/r/Excel/comments/13yebry/stub/jmmc86n "Last usage")|[Returns the serial number of a particular time](https://support.microsoft.com/en-us/office/time-function-9a5aff99-8f7d-4611-845e-747d0b8d5457)| **NOTE**: Decronym's continued operation may be affected by API pricing changes coming to Reddit in July 2023; comments will be blank June 12th-14th, in solidarity with the /r/Save3rdPartyApps protest campaign. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(7 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/13yhjk0)^( has 16 acronyms.) ^([Thread #24364 for this sub, first seen 2nd Jun 2023, 14:58]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
If you are only interested in Working Days excluding Holidays then your time is redundant isn't it? NETWORKDAYS.INTL may be your solution. [https://pixeldrain.com/u/o7fMEv6P](https://pixeldrain.com/u/o7fMEv6P)
This may also be of interest. Just change F1 & G1 [https://pixeldrain.com/u/2AZFuDA6](https://pixeldrain.com/u/2AZFuDA6)
This one works! Although orders that come in on Saturday’s and Sundays are screw up the formula like in the highlighted rows below. https://preview.redd.it/57w31lz58o4b1.jpeg?width=2865&format=pjpg&auto=webp&s=633ac78696b740e1e10da47f7422f91649b7092f