Complicated work schedule log calculations.

LucidMovement

New Member
Joined
Jun 6, 2015
Messages
1
So, I have a work log that looks kind of like this....

Start 9:00am
-stuff
-more stuff
Stop 3:30pm
Start 4:30pm
-even more stuff
Stop 6:30pm
Start 9:30pm
- lots more stuff
End 1:00am

Occasionally it will look like this...
Start 7:00am
- too much stuff
Stop 3:00am

I'm copying it all out into different columns where the row is the date. However, the number of stops and starts is not always consistent. One day will have a Start and End only, another day can have 5 pairs of starts and stops. I'd like to total the number.

Currently I'm using this formula, which is failing. I'm rather excel ignorant... There are a few assumptions. There will always be a Start and End. If there is a start and no stop, then it uses the end (i3 in this case).

=SUM(IF(ISBLANK(C3),I3-B3,C3-B3+IF(ISBLANK(E3),I3-D3,E3-D3+IF(ISBLANK(G3),I3-F3,G3-F3+I3-H3)*24)

The desire is to have an hourly total per day. The end goal being to plot this as a graph to view working trends and sum entire ranges of dates. I'm sure there is a relatively simple way of doing this, but I'm kind of clueless.

Bonus question: Is it possible to take the above mentioned format and auto-parse it to extract start and stop times?

Even more bonus question: Is it possible to assume dates based on an initial date? The log is based on weeks, so the days are not individually dated, only the start date for that week is. (Horrible data formatting I know, I'm a bad bad art monkey).

Any help would be greatly appreciated! Thank you in advance.


 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

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