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