Help on hour difference

[sksanjeev786]

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

 

[Ezguy4u]

sksanjeev786 Ok 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
	A	B	C	D	E	F
1	Due Date	Deliver	Diff hour with in 9:6 PM		Due Calculation	Deliver Calculation
2	Thu 9/17/2020 17:30	Wed 9/16/2020 11:00	15:30		8:30	7:00
3	Wed 9/23/2020 15:15	Tue 9/22/2020 11:45	12:30		6:15	6:15
4	Wed 10/7/2020 16:15	Tue 10/6/2020 13:45	11:30		7:15	4:15
Diff Hour

Cell Formulas
Range	Formula
E2:E4	E2	=TEXT(A2,"h:mm")-TEXT("9:00 AM","h:mm")
F2:F4	F2	=TEXT("6:00 PM","h:mm")-TEXT(B2,"h:mm")
C2:C4	C2	=E2+F2

 

[sksanjeev786]

sksanjeev786 Ok 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
	A	B	C	D	E	F
1	Due Date	Deliver	Diff hour with in 9:6 PM		Due Calculation	Deliver Calculation
2	Thu 9/17/2020 17:30	Wed 9/16/2020 11:00	15:30		8:30	7:00
3	Wed 9/23/2020 15:15	Tue 9/22/2020 11:45	12:30		6:15	6:15
4	Wed 10/7/2020 16:15	Tue 10/6/2020 13:45	11:30		7:15	4:15
Diff Hour

Cell Formulas
Range	Formula
E2:E4	E2	=TEXT(A2,"h:mm")-TEXT("9:00 AM","h:mm")
F2:F4	F2	=TEXT("6:00 PM","h:mm")-TEXT(B2,"h:mm")
C2:C4	C2	=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]

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]

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

 

[Ezguy4u]

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

 

[sksanjeev786]

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
	A	B	C	D	E	F
1	Due Date	Deliver	Diff hour with in 9:6 PM		Due Calculation	Deliver Calculation
2	Thu 9/17/2020 17:30	Wed 9/16/2020 11:00	#VALUE!		#VALUE!	#VALUE!
3	Wed 9/23/2020 15:15	Tue 9/22/2020 11:45	#VALUE!		#VALUE!	#VALUE!
4	Wed 10/7/2020 16:15	Tue 10/6/2020 13:45	#VALUE!		#VALUE!	#VALUE!
Sheet1

Cell Formulas
Range	Formula
E2:E4	E2	=TEXT(A2,"h:mm")-TEXT("9:00 AM","h:mm")
F2:F4	F2	=TEXT("6:00 PM","h:mm")-TEXT(B2,"h:mm")
C2:C4	C2	=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]

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

Thank you Fluff

 

[Ezguy4u]

sksanjeev786 Ok here is the deal. We have to focus on columns A & B. In my opinion, formatting can be very difficult. I believe that you can input data and it looks like a date but it isn't. You can even try and change the format but no joy. Select cell data in column A and try to switch it to Text. Like the example below, it should change to a number. If it doesn't change to a number then it isn't formatted correctly. The only thing I can suggest is to start with a new workbook and format columns A & B with that custom formatting. We will get this.​

Book1
	A	B	C	D	E	F
1	Due Date	Deliver
2	Wed 9/18/2024 17:30	Tue 9/17/2024 10:00	16:30		8:30	8:00
3	44097.63542	Mon 9/23/2024 11:45
4	Tue 10/8/2024 16:15	Mon 10/7/2024 13:45
Sheet4

Cell Formulas
Range	Formula
C2	C2	=E2+F2
E2	E2	=TEXT(A2,"h:mm")-TEXT("9:00 AM","h:mm")
F2	F2	=TEXT("6:00 PM","h:mm")-TEXT(B2,"h:mm")

 

[sksanjeev786]

sksanjeev786 Ok here is the deal. We have to focus on columns A & B. In my opinion, formatting can be very difficult. I believe that you can input data and it looks like a date but it isn't. You can even try and change the format but no joy. Select cell data in column A and try to switch it to Text. Like the example below, it should change to a number. If it doesn't change to a number then it isn't formatted correctly. The only thing I can suggest is to start with a new workbook and format columns A & B with that custom formatting. We will get this.​

Book1
	A	B	C	D	E	F
1	Due Date	Deliver
2	Wed 9/18/2024 17:30	Tue 9/17/2024 10:00	16:30		8:30	8:00
3	44097.63542	Mon 9/23/2024 11:45
4	Tue 10/8/2024 16:15	Mon 10/7/2024 13:45
Sheet4

Cell Formulas
Range	Formula
C2	C2	=E2+F2
E2	E2	=TEXT(A2,"h:mm")-TEXT("9:00 AM","h:mm")
F2	F2	=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]

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