# 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

### 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
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
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
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

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
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

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
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 Date Deliver Diff hour with in 9:6 PM day 2mro DA2mro Thu 17-Sep-20 17:30 Wed 16-Sep-20 11:00 15:30 7 8:30 Wed 23-Sep-20 10:30 Tue 22-Sep-20 15:30 4.00 2.30 1.30 Tue 6-Oct-20 11:50 Mon 5-Oct-20 10:50 9:40 6:50 2.5 Fri 9-Oct-20 11:00 Mon 12-Oct-20 17:00 it will be in negative if reverse the date it will be 3 hr 1 2 Wed 14-Oct-20 20:00 Mon 12-Oct-20 17:00 19:00 1 9 9

Regards,
Sanjeev

#### Ezguy4u

##### Board Regular
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
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).

Regards,
Sanjeev.

Replies
2
Views
123
Replies
8
Views
128
Replies
1
Views
103
Replies
2
Views
41
Replies
4
Views
243