# Help on hour difference

#### sksanjeev786

##### Board Regular
Hello Team,

I have an office timing from 9:00A M to 6:00 PM excluding Sat and Sun.

I need a different hour mention in the below sheet. and calculation need between office timeing

 Due Date Deliver Diff hour with in 9:6 PM 9/4/2020 21:30 9/3/2020 11:00 7:30 hr 9/3/2020 9:30 9/2/2020 15:30 3 hr 9/2/2020 11:50 9/2/2020 10:50 1 hr 9/7/2020 11:00 9/4/2020 17:00 3 hr

#### sksanjeev786Ok we are going to start out slow and build on it. Now I have picked dates and times that are simple. Once you agree to these examples we can go to the next level. We will pick dates and times that won't work with these formulas and come up with more formulas that will work. I want to also bring up cell formatting. Format Columns A & B using below custom format​

Format Columns C, E & F with

With any luck, maybe one of the A Students will look at this problem and come up with more solutions.

20-10-18 time1.xlsx
ABCDEF
1Due DateDeliverDiff hour with in 9:6 PMDue CalculationDeliver Calculation
2Thu 9/17/2020 17:30Wed 9/16/2020 11:0015:308:307:00
3Wed 9/23/2020 15:15Tue 9/22/2020 11:4512:306:156:15
4Wed 10/7/2020 16:15Tue 10/6/2020 13:4511:307:154:15
Diff Hour
Cell Formulas
RangeFormula
E2:E4E2=TEXT(A2,"h:mm")-TEXT("9:00 AM","h:mm")
F2:F4F2=TEXT("6:00 PM","h:mm")-TEXT(B2,"h:mm")
C2:C4C2=E2+F2

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### sksanjeev786Ok we are going to start out slow and build on it. Now I have picked dates and times that are simple. Once you agree to these examples we can go to the next level. We will pick dates and times that won't work with these formulas and come up with more formulas that will work. I want to also bring up cell formatting. Format Columns A & B using below custom format​

View attachment 24428

Format Columns C, E & F with

View attachment 24430

With any luck, maybe one of the A Students will look at this problem and come up with more solutions.

20-10-18 time1.xlsx
ABCDEF
1Due DateDeliverDiff hour with in 9:6 PMDue CalculationDeliver Calculation
2Thu 9/17/2020 17:30Wed 9/16/2020 11:0015:308:307:00
3Wed 9/23/2020 15:15Tue 9/22/2020 11:4512:306:156:15
4Wed 10/7/2020 16:15Tue 10/6/2020 13:4511:307:154:15
Diff Hour
Cell Formulas
RangeFormula
E2:E4E2=TEXT(A2,"h:mm")-TEXT("9:00 AM","h:mm")
F2:F4F2=TEXT("6:00 PM","h:mm")-TEXT(B2,"h:mm")
C2:C4C2=E2+F2

Hi Sir,

while applying the above formula i am getting error in the formula as i have used same both formate you have shown above.

 Due Date Deliver Diff hour with in 9:6 PM Due Calculation Deliver Calculation Thu 9/17/2020 17:30 Wed 9/16/2020 11:00 #VALUE! #VALUE! #VALUE! Wed 9/23/2020 15:15 Tue 9/22/2020 11:45 #VALUE! #VALUE! #VALUE! Wed 10/7/2020 16:15 Tue 10/6/2020 13:45 #VALUE! #VALUE! #VALUE!

Regards,
Sanjeev.

#### Ezguy4u

##### Board Regular
Sanjeev. I don't understand why your XL2BB looks so much different than mine. We need to work on this. I need to see which rows and columns you are using. I need to see which formulas are in which cells. So when you use MrExcel tab, don't forget to select the area you want to copy, and check that box Cell Formulas. Let me ask you, do you understand the formulas in the cells I have posted above?

#### Fluff

##### MrExcel MVP, Moderator
@sksanjeev786
When using the XL2BB add-in please use the "Generate Output" button, rather then the "Table Only" option.

#### Ezguy4u

##### Board Regular

Thank you Fluff. The limits to my ignorance is unlimited.

#### sksanjeev786

##### Board Regular
Hi Sir,

Sorry for the below formate as suggested I have taken the formate and also apply the same formate above screenshot and getting an error as per the below table

MR Excel_1016.xlsx
ABCDEF
1Due DateDeliverDiff hour with in 9:6 PMDue CalculationDeliver Calculation
2Thu 9/17/2020 17:30Wed 9/16/2020 11:00#VALUE!#VALUE!#VALUE!
3Wed 9/23/2020 15:15Tue 9/22/2020 11:45#VALUE!#VALUE!#VALUE!
4Wed 10/7/2020 16:15Tue 10/6/2020 13:45#VALUE!#VALUE!#VALUE!
Sheet1
Cell Formulas
RangeFormula
E2:E4E2=TEXT(A2,"h:mm")-TEXT("9:00 AM","h:mm")
F2:F4F2=TEXT("6:00 PM","h:mm")-TEXT(B2,"h:mm")
C2:C4C2=E2+F2

Sanjeev. I don't understand why your XL2BB looks so much different than mine. We need to work on this. I need to see which rows and columns you are using. I need to see which formulas are in which cells. So when you use MrExcel tab, don't forget to select the area you want to copy, and check that box Cell Formulas. Let me ask you, do you understand the formulas in the cells I have posted above?
Sanjeev. I don't understand why your XL2BB looks so much different than mine. We need to work on this. I need to see which rows and columns you are using. I need to see which formulas are in which cells. So when you use MrExcel tab, don't forget to select the area you want to copy, and check that box Cell Formulas. Let me ask you, do you understand the formulas in the cells I have posted above?

#### sksanjeev786

##### Board Regular

@sksanjeev786
When using the XL2BB add-in please use the "Generate Output" button, rather then the "Table Only" option.

Thank you Fluff

#### Book1ABCDEF1Due DateDeliver2Wed 9/18/2024 17:30Tue 9/17/2024 10:0016:308:308:00344097.63542Mon 9/23/2024 11:454Tue 10/8/2024 16:15Mon 10/7/2024 13:45Sheet4Cell FormulasRangeFormulaC2C2=E2+F2E2E2=TEXT(A2,"h:mm")-TEXT("9:00 AM","h:mm")F2F2=TEXT("6:00 PM","h:mm")-TEXT(B2,"h:mm")

Hi Sir,

I check now and it works for me and I have removed Wed and Tue from the cell and then I got the hours details.

Thank you so much for your help and I will check again and will get back to you again if I need any support.

Thank you once again, sir.
Sanjeev

#### Ezguy4u

##### Board Regular
Glad to help. Now if you want to continue on, let us know

Replies
2
Views
133
Replies
8
Views
140
Replies
1
Views
112
Replies
1
Views
74
Replies
2
Views
43