sumif based on time.

CPTN2469

New Member
Joined
Nov 28, 2017
Messages
2
good afternoon, this is my first post on here and hoping i have come to the right spot. i have been searching for weeks without coming up with an answer that works for me. I work as a manager for ports in long beach california and we track move counts in an excel document by the hours. we break down the day into 4 periods of 2 hours a piece. in an attempt to make my life easier i have been trying to come up with a formula to use that i can just punch in the numbers and based off of the time they will go into a certain box. i do not have any idea if this is possible and i am open to other ideas as well. i have a cheap work around right now that is okay but trying to make it even easier. i will attempt to attach pictures to show what i am looking at and hopefully someone can help me out.

i have attached a copy of an example of a day. i would like to be able to just punch in the totals in the right hand side and based on the time (labeled at the top) it put in the difference there. so for example if i punch in at 1000 hours 60 i want it to go into the box for period 1 between the times of 0800 and 1000 hours. but then if at lets say 1130 i punch in 125 i want the difference of period 1 and that total to go into period 2 as it is in between those hours. so it would input 65 in period 2 cell.

[TABLE="width: 833"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Period 1
[/TD]
[TD]Period 2
[/TD]
[TD]Lunch
[/TD]
[TD]Period 3
[/TD]
[TD]Period 4
[/TD]
[TD]OT 1
[/TD]
[TD]OT 2
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CRANE
[/TD]
[TD]0800-1000
[/TD]
[TD]1000-1200
[/TD]
[TD]1200-1300
[/TD]
[TD]1300-1500
[/TD]
[TD]1500-1700
[/TD]
[TD][/TD]
[TD][/TD]
[TD]TOTAL
[/TD]
[TD]To Go
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]52
[/TD]
[TD]45
[/TD]
[TD][/TD]
[TD]49
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]146
[/TD]
[TD="align: right"]116
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]43
[/TD]
[TD]52
[/TD]
[TD][/TD]
[TD]41
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]136
[/TD]
[TD="align: right"]156
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]52
[/TD]
[TD]53
[/TD]
[TD][/TD]
[TD]44
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]149
[/TD]
[TD="align: right"]101
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]51
[/TD]
[TD]53
[/TD]
[TD][/TD]
[TD]63
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]167
[/TD]
[TD="align: right"]122
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]44
[/TD]
[TD]35
[/TD]
[TD][/TD]
[TD]58
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]137
[/TD]
[TD="align: right"]115
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]38
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]38
[/TD]
[TD="align: right"]200
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MOVES
[/TD]
[TD]280
[/TD]
[TD]238
[/TD]
[TD][/TD]
[TD]255
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]773
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GANG HOURS
[/TD]
[TD]12.0
[/TD]
[TD]10.0
[/TD]
[TD]0.0
[/TD]
[TD]10.0
[/TD]
[TD]0.0
[/TD]
[TD]0.0
[/TD]
[TD]0.0
[/TD]
[TD]32.0
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DETENTIONS
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.0
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NET MPH
[/TD]
[TD]23.3
[/TD]
[TD]23.8
[/TD]
[TD]#DIV/0!
[/TD]
[TD]25.5
[/TD]
[TD]#DIV/0!
[/TD]
[TD]#DIV/0!
[/TD]
[TD]#DIV/0!
[/TD]
[TD]24.2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GROSS MPH
[/TD]
[TD]23.3
[/TD]
[TD]23.8
[/TD]
[TD]#DIV/0!
[/TD]
[TD]25.5
[/TD]
[TD]#DIV/0!
[/TD]
[TD]#DIV/0!
[/TD]
[TD]#DIV/0!
[/TD]
[TD]24.2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SHIP GROSS MPH
[/TD]
[TD]24.2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]











[TABLE="width: 375"]
<tbody>[TR]
[TD]period 1
[/TD]
[TD]period 2
[/TD]
[TD]period 3
[/TD]
[TD]period 4
[/TD]
[/TR]
[TR]
[TD="align: right"]52
[/TD]
[TD="align: right"]97
[/TD]
[TD="align: right"]146
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]43
[/TD]
[TD="align: right"]95
[/TD]
[TD="align: right"]136
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]52
[/TD]
[TD="align: right"]105
[/TD]
[TD="align: right"]149
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]51
[/TD]
[TD="align: right"]104
[/TD]
[TD="align: right"]167
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]44
[/TD]
[TD="align: right"]79
[/TD]
[TD="align: right"]137
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]38
[/TD]
[TD="align: right"]38
[/TD]
[TD="align: right"]38
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
the way i have it set up now is that i have a seperate chart where i type in the total counts and basically period 1 = its box in its entirity. where as when i type in 97 for period 2 on 45 goes into the box in the original document that i use to send emails in. I hope this makes sense. I know the very basics of excell and am trying to learn more as we use it daily and formulas would make things a lot easier at my work.

Thank you all for your time and please feel free to ask any follow up questions if needed.
the above examples do corrilate with how i have it set up thank you
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
i forgot to add the way that i have it now, if i do not have a number entered into the the period 2, 3, 4 it shows a - number which then affects the total counts on the top excel document. if i can not do it based off of time. is there a way to do a sumif and on show a number in the top cell if there is a number greater than 0 in the bottom chart?

for example i put 38 in cell a10 in the bottom chart on the top chart it goes into cell a1 as 38. but in cell b1 it will say -38 because the formula is cell b10-a10 for cell b1. i hope this makes sense. i am not good at this excel stuff and trying to learn it on my own is proving difficult.
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top