Help on hour difference

sksanjeev786

Board Regular
Joined
Aug 5, 2020
Messages
200
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
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Ezguy4u

Board Regular
Joined
Feb 10, 2010
Messages
139
Office Version
  1. 2019
Platform
  1. Windows
sksanjeev786 Maybe somebody else understands your Diff hour answer but if you could clarify a couple of the numbers, that would be neat. For instance, the first Deliver was at 11:00 AM. The difference between 6 PM and 11 AM is just 7 hours. If I am missing something here let me know. Also the 15:30 should be 2 hours and 30 minutes. And that last one is 17:00 hours or 5 PM. So the difference between 6 PM and 6 PM is 1 Hour. Unless I am missing something here, let me know if my cyphering is wrong. It all just helps in coming up with a good formula
 

sksanjeev786

Board Regular
Joined
Aug 5, 2020
Messages
200
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
sksanjeev786 Maybe somebody else understands your Diff hour answer but if you could clarify a couple of the numbers, that would be neat. For instance, the first Deliver was at 11:00 AM. The difference between 6 PM and 11 AM is just 7 hours. If I am missing something here let me know. Also the 15:30 should be 2 hours and 30 minutes. And that last one is 17:00 hours or 5 PM. So the difference between 6 PM and 6 PM is 1 Hour. Unless I am missing something here, let me know if my cyphering is wrong. It all just helps in coming up with a good formula

Hello Sir,

So my office timing is 9:00 AM to 6:00 PM and yes sorry it moring 9:30 AM not 21:30 so my productive hour is 9 -6 so I have delivered project 1 day ago at 11 AM so different would be (11AM -6PM =7 ) and next day office start at 9 (but due is 9:30= .5 hr effecienty )= 7:30 hour.

and for 3:30 PM (3:30 to 6 PM= 2:30) and next day office start 9 (so again .5 hour.)


Hope you can able to understand.

Thanks.

Regards,
Sanjeev.
 

sksanjeev786

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

So my office timing is 9:00 AM to 6:00 PM and yes sorry it moring 9:30 AM not 21:30 so my productive hour is 9 -6 so I have delivered project 1 day ago at 11 AM so different would be (11AM -6PM =7 ) and next day office start at 9 (but due is 9:30= .5 hr effecienty )= 7:30 hour.

and for 3:30 PM (3:30 to 6 PM= 2:30) and next day office start 9 (so again .5 hour.)


Hope you can able to understand.

Thanks.

Regards,
Sanjeev.


Hi Team

Can anyone help me on this.

Thanks.
Regards,
Sanjeev
 

Ezguy4u

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

ADVERTISEMENT

sksanjeev786 Ok let's see if we can continue this discussion and come up with a solution. Now I am going to say you are going to have to repost that Excel worksheet and make sure there are no errors. Maybe restate the office timing and clarify this 9:00 AM and 9:30 AM statement. Next just write out what calculations you used to get your answers for each example. The more information we get the better the answer you are going to get.
 

sksanjeev786

Board Regular
Joined
Aug 5, 2020
Messages
200
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
H
sksanjeev786 Ok let's see if we can continue this discussion and come up with a solution. Now I am going to say you are going to have to repost that Excel worksheet and make sure there are no errors. Maybe restate the office timing and clarify this 9:00 AM and 9:30 AM statement. Next just write out what calculations you used to get your answers for each example. The more information we get the better the answer you are going to get.

Hi Sir,

So let start with as my office timing is 9:00 AM to 6:00 PM and I have to deliver my project within this time for example

my project due at 11:00 AM but I have sent 9:30 AM so my efficiency hour will be 1.30 hours. and other projects due at 5:30 PM but I have sent 11:00 AM so my efficiency hour will be 6.30 hours. and if the project due tomorrow at 10:00 AM but I have sent today only like 4:00 PM so my efficiency hour will be (Today= 2 hours. till 6:00 PM will count ) and (tomorrow =1 hour, as office stat at 9:00 AM) so total efficiency hour will 3 hours.

Hope this will understand the scenario the same I have mentioned in the excel file in above.

Thanks.
Regards,
Sanjeev
 

Ezguy4u

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

ADVERTISEMENT

sksanjeev786 The good news is I am retired and got nothing else going on. I am committed to figuring this out. Now your numbers just don't line up. And you didn't repost your worksheet so I am going to post this worksheet and you will now fill in the answers. These are different times and dates so you can also show your work on how you got the answer. This exercise will give us more information. Go Dodgers



Book1
ABC
1Due DateDeliverDiff hour with in 9:6 PM
2Thu 17-Sep-20 17:30Wed 16-Sep-20 11:00
3Wed 23-Sep-20 10:30Tue 22-Sep-20 15:30
4Tue 6-Oct-20 11:50Mon 5-Oct-20 10:50
5Fri 9-Oct-20 11:00Mon 12-Oct-20 17:00
6Wed 14-Oct-20 20:00Mon 12-Oct-20 17:00
Sheet1A
 

sksanjeev786

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

I have fill the details in the above sheet for row 6 due date can note exceed after 6:00 PM if it exceed we need calcatuoin time between 9-6 only.

Thanks so much for your help sir.

Due DateDeliverDiff hour with in 9:6 PMday2mroDA2mro
Thu 17-Sep-20 17:30Wed 16-Sep-20 11:0015:3078:30
Wed 23-Sep-20 10:30Tue 22-Sep-20 15:304.002.301.30
Tue 6-Oct-20 11:50Mon 5-Oct-20 10:509:406:502.5
Fri 9-Oct-20 11:00Mon 12-Oct-20 17:00it will be in negative if reverse the date it will be 3 hr12
Wed 14-Oct-20 20:00Mon 12-Oct-20 17:0019:00199


Regards,
Sanjeev
 

Ezguy4u

Board Regular
Joined
Feb 10, 2010
Messages
139
Office Version
  1. 2019
Platform
  1. Windows
Ok now we are getting some place. We still have questions so let's get them answered. Now I made a few changes like I want to make sure we are all on the same page. So I changed the military time to just regular AM PM. Those 3 extra columns heading. What does day refer to. What does 2mro mean. And DA2mro refers to what. Let's take each line and show your work.
On the first line how did you get 7 hours using 5:30 PM and 11:00 AM. How did you get 8 hours and 30 minutes using 5:30 PM and 11:00 AM? Does the date number have any thing to do with the calculations?
Go line by line and show your work how you got the hours and minutes. We can come up with a solution.

Book1
ABCDEF
1Due DateDeliverDiff hour with in 9:6 PMday2mroDA2mro
2Thu 17-Sep-20 5:30 PMWed 16-Sep-20 11:00 AM15:3078:30
3Wed 23-Sep-20 10:30 AMTue 22-Sep-20 3:30 PM42.31.3
4Tue 6-Oct-20 11:50 AMMon 5-Oct-20 10:50 AM9:406:502.5
5Mon 12-Oct-2020 05:00 PMFri 09-Oct-2020 11:00 AMOk I reversed the dates 312
6Wed 14-Oct-20 8:00 PMMon 12-Oct-20 5:00 PM19:00199
Sheet1
 

sksanjeev786

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

Those 3 color is like for reference of day like Day, 2mro(Tomorrow) and DF2mro(Day after Tomorrow). So in column D first line, i got 7 as my shift timing is 9-6 (on 16th 11:00 AM to 6:00 PM= 7 hour for day, and the next day 9:00 AM to 5:30= 8:30) as the due date is 17 Sep.
I hope this will clear the situation. so basically data productive hour will not more then 9 hour every day it due and deliver is between (9:00 AM to 6:00 AM).

Thanks for your time sir.

Regards,
Sanjeev.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,279
Messages
5,571,286
Members
412,375
Latest member
BRJoeyMelo
Top