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

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]

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]

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]

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]

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
	A	B	C
1	Due Date	Deliver	Diff hour with in 9:6 PM
2	Thu 17-Sep-20 17:30	Wed 16-Sep-20 11:00
3	Wed 23-Sep-20 10:30	Tue 22-Sep-20 15:30
4	Tue 6-Oct-20 11:50	Mon 5-Oct-20 10:50
5	Fri 9-Oct-20 11:00	Mon 12-Oct-20 17:00
6	Wed 14-Oct-20 20:00	Mon 12-Oct-20 17:00
Sheet1A

 

[sksanjeev786]

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]

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
	A	B	C	D	E	F
1	Due Date	Deliver	Diff hour with in 9:6 PM	day	2mro	DA2mro
2	Thu 17-Sep-20 5:30 PM	Wed 16-Sep-20 11:00 AM	15:30	7	8:30
3	Wed 23-Sep-20 10:30 AM	Tue 22-Sep-20 3:30 PM	4	2.3	1.3
4	Tue 6-Oct-20 11:50 AM	Mon 5-Oct-20 10:50 AM	9:40	6:50	2.5
5	Mon 12-Oct-2020 05:00 PM	Fri 09-Oct-2020 11:00 AM	Ok I reversed the dates 3	1	2
6	Wed 14-Oct-20 8:00 PM	Mon 12-Oct-20 5:00 PM	19:00	1	9	9
Sheet1

 

[sksanjeev786]

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.