Help on hour difference

sksanjeev786

Board Regular
Joined
Aug 5, 2020
Messages
201
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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 DateDeliverDiff hour with in 9:6 PM
9/4/2020 21:309/3/2020 11:007:30 hr
9/3/2020 9:309/2/2020 15:303 hr
9/2/2020 11:509/2/2020 10:501 hr
9/7/2020 11:009/4/2020 17:003 hr
 

Ezguy4u

Board Regular
Joined
Feb 10, 2010
Messages
139
Office Version
  1. 2019
Platform
  1. Windows

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


1603058658756.png


Format Columns C, E & F with

1603058769781.png


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
 

Some videos you may like

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

sksanjeev786

Board Regular
Joined
Aug 5, 2020
Messages
201
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

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
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 DateDeliverDiff hour with in 9:6 PMDue CalculationDeliver Calculation
Thu 9/17/2020 17:30Wed 9/16/2020 11:00#VALUE!#VALUE!#VALUE!
Wed 9/23/2020 15:15Tue 9/22/2020 11:45#VALUE!#VALUE!#VALUE!
Wed 10/7/2020 16:15Tue 10/6/2020 13:45#VALUE!#VALUE!#VALUE!


Regards,
Sanjeev.
 

Ezguy4u

Board Regular
Joined
Feb 10, 2010
Messages
139
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
51,087
Office Version
  1. 365
Platform
  1. Windows
@sksanjeev786
When using the XL2BB add-in please use the "Generate Output" button, rather then the "Table Only" option.
 

Ezguy4u

Board Regular
Joined
Feb 10, 2010
Messages
139
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

sksanjeev786

Board Regular
Joined
Aug 5, 2020
Messages
201
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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?
 

Ezguy4u

Board Regular
Joined
Feb 10, 2010
Messages
139
Office Version
  1. 2019
Platform
  1. Windows

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
ABCDEF
1Due DateDeliver
2Wed 9/18/2024 17:30Tue 9/17/2024 10:0016:308:308:00
344097.63542Mon 9/23/2024 11:45
4Tue 10/8/2024 16:15Mon 10/7/2024 13:45
Sheet4
Cell Formulas
RangeFormula
C2C2=E2+F2
E2E2=TEXT(A2,"h:mm")-TEXT("9:00 AM","h:mm")
F2F2=TEXT("6:00 PM","h:mm")-TEXT(B2,"h:mm")

 

sksanjeev786

Board Regular
Joined
Aug 5, 2020
Messages
201
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

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
ABCDEF
1Due DateDeliver
2Wed 9/18/2024 17:30Tue 9/17/2024 10:0016:308:308:00
344097.63542Mon 9/23/2024 11:45
4Tue 10/8/2024 16:15Mon 10/7/2024 13:45
Sheet4
Cell Formulas
RangeFormula
C2C2=E2+F2
E2E2=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
Joined
Feb 10, 2010
Messages
139
Office Version
  1. 2019
Platform
  1. Windows
Glad to help. Now if you want to continue on, let us know
 

Watch MrExcel Video

Forum statistics

Threads
1,119,192
Messages
5,576,598
Members
412,737
Latest member
mjo1234
Top