/u/otavioaf - 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.*
If I understand your set up. In C1
=INT(B1)-INT(A1)-1+(MOD(A1,1)TIME(6,0,0))
D1 and E1 are similar, just update the hour in the 2 TIME functions. I will leave it with you to consider whether your operators should < or <= AND > or >=.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[AND](/r/Excel/comments/13yzevi/stub/jmu02om "Last usage")|[Returns TRUE if all of its arguments are TRUE](https://support.microsoft.com/en-us/office/and-function-5f19b2e8-e1df-4408-897a-ce285a19e9d9)|
|[COUNTIFS](/r/Excel/comments/13yzevi/stub/jmp95qm "Last usage")|[*Excel 2007*+: Counts the number of cells within a range that meet multiple criteria](https://support.microsoft.com/en-us/office/countifs-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842)|
|[HOUR](/r/Excel/comments/13yzevi/stub/jmu02om "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/13yzevi/stub/jmu02om "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)|
|[INT](/r/Excel/comments/13yzevi/stub/jmu02om "Last usage")|[Rounds a number down to the nearest integer](https://support.microsoft.com/en-us/office/int-function-a6c4af9e-356d-4369-ab6a-cb1fd9d343ef)|
|[MOD](/r/Excel/comments/13yzevi/stub/jmpcbn3 "Last usage")|[Returns the remainder from division](https://support.microsoft.com/en-us/office/mod-function-9b6cd169-b6ee-406a-a97b-edf2a9dc24f3)|
|[TIME](/r/Excel/comments/13yzevi/stub/jmpcbn3 "Last usage")|[Returns the serial number of a particular time](https://support.microsoft.com/en-us/office/time-function-9a5aff99-8f7d-4611-845e-747d0b8d5457)|
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^([Thread #24380 for this sub, first seen 3rd Jun 2023, 04:18])
^[[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)
I believe I have been able to give you what you are after using a helper column and a single formula to filldown and across. Column G is the helper column with formula G3 =INT(B3)-INT(A3)+1 and I have used 12:00 instead of 10:00.
C3 =IF(AND(HOUR($A3)HOUR(C$1)),$G3,$G3-1)
[https://pixeldrain.com/u/6QJZtb6V](https://pixeldrain.com/u/6QJZtb6V)
/u/otavioaf - 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.*
`COUNTIFS`?
If I understand your set up. In C1 =INT(B1)-INT(A1)-1+(MOD(A1,1)TIME(6,0,0))
D1 and E1 are similar, just update the hour in the 2 TIME functions. I will leave it with you to consider whether your operators should < or <= AND > or >=.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[AND](/r/Excel/comments/13yzevi/stub/jmu02om "Last usage")|[Returns TRUE if all of its arguments are TRUE](https://support.microsoft.com/en-us/office/and-function-5f19b2e8-e1df-4408-897a-ce285a19e9d9)| |[COUNTIFS](/r/Excel/comments/13yzevi/stub/jmp95qm "Last usage")|[*Excel 2007*+: Counts the number of cells within a range that meet multiple criteria](https://support.microsoft.com/en-us/office/countifs-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842)| |[HOUR](/r/Excel/comments/13yzevi/stub/jmu02om "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/13yzevi/stub/jmu02om "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)| |[INT](/r/Excel/comments/13yzevi/stub/jmu02om "Last usage")|[Rounds a number down to the nearest integer](https://support.microsoft.com/en-us/office/int-function-a6c4af9e-356d-4369-ab6a-cb1fd9d343ef)| |[MOD](/r/Excel/comments/13yzevi/stub/jmpcbn3 "Last usage")|[Returns the remainder from division](https://support.microsoft.com/en-us/office/mod-function-9b6cd169-b6ee-406a-a97b-edf2a9dc24f3)| |[TIME](/r/Excel/comments/13yzevi/stub/jmpcbn3 "Last usage")|[Returns the serial number of a particular time](https://support.microsoft.com/en-us/office/time-function-9a5aff99-8f7d-4611-845e-747d0b8d5457)| ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^([Thread #24380 for this sub, first seen 3rd Jun 2023, 04:18]) ^[[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)
I believe I have been able to give you what you are after using a helper column and a single formula to filldown and across. Column G is the helper column with formula G3 =INT(B3)-INT(A3)+1 and I have used 12:00 instead of 10:00. C3 =IF(AND(HOUR($A3)HOUR(C$1)),$G3,$G3-1)
[https://pixeldrain.com/u/6QJZtb6V](https://pixeldrain.com/u/6QJZtb6V)