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.


 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Watch MrExcel Video

Forum statistics

Threads
1,099,521
Messages
5,469,136
Members
406,638
Latest member
Jack_Johnson

This Week's Hot Topics

Top