SUM from multiple formulas (time difference calc)

[Endosmok]

Ok. I have 7 columns based off each day of the week.
There is a START row and a STOP row for start/stop times under each day of the week.
They are in military time format.

I can get the time difference between START and STOP with a simple formula
=STOP-START

I can also SUM up the time difference for each day of the week into one cell (though it's a long formula) but it works.

Here's where I run into an issue. When the time goes into the next day you get ###### when calculating the time difference.
example, Start Time of 2000 with an End Time of 0200. I found a fix for that off the internet.
The new formula to handle this issue is =IF(STOPTIME>STARTTIME,STOPTIME-STARTTIME,STOPTIME+1-STARTTIME)
example, =IF(B9>B8,B9-B8,B9+1-B8) this works fine for the ##### issue and gives the correct result.

So my issue is this.... I don't know how to SUM up everything into one cell now that I have to use that =IF statement

So logically (not thinking in excel) I figure it would be...

=IF(B9>B8,B9-B8,B9+1-B8) + =IF(CB9>C8,C9-C8,C9+1-C8) + next day of week + next day of week + etc would equal the SUM of the time difference total for the whole week.

I can't figure out how to get this to work for a result in a single cell.

The other solution I can think of is use the above working IF formula in a hidden row for each day of the week and have it total to a visible cell but I don't know if that would be the right way to go about this.

Any suggestions would be greatly appreciated. I almost have this working... except this last issue.

Thanks

 

[oldbrewer]

Re: Need help with SUM from multiple formulas (time difference calc)

why not use date time format eg 15/10/2017 22:17:41

then subtraction from Tue to Mon eg is seamless

 

[Endosmok]

Re: Need help with SUM from multiple formulas (time difference calc)

why not use date time format eg 15/10/2017 22:17:41

then subtraction from Tue to Mon eg is seamless

but how would you get a grand total ... I can do the subtraction just fine. It's the total sum of the time difference for the entire week that I cannot get. So are you suggesting that I switch the time/date format in order to have an easier sum calculation?

I believe I tried the a similar time format above but I couldn't get it to work properly, but I am a excel amateur so there's a possibility I was going about it wrong.

 

[Endosmok]

Re: Need help with SUM from multiple formulas (time difference calc)

Also, I am trying to make time entry as seamless as possible. So currently, if I type 1545 it gets converted to 15:45 automatically. I thought the below format I had to enter in everything properly. I don't necessarily need to know the exact day with a date, but more a single shift. Shift time is calculated on a 24 hour basis, so if one day runs into the next it still calculated as one day within the 24 hr period.

why not use date time format eg 15/10/2017 22:17:41

then subtraction from Tue to Mon eg is seamless

 

[oldbrewer]

Re: Need help with SUM from multiple formulas (time difference calc)

1545 is converted to 15:45 a true time so convert it to today() + 15:45


given you have 5 starts and 5 stops do you want the total time worked ?

 

[Endosmok]

Re: Need help with SUM from multiple formulas (time difference calc)

1545 is converted to 15:45 a true time so convert it to today() + 15:45


given you have 5 starts and 5 stops do you want the total time worked ?

Total time was/is the issue I have right now using the IF formula above. I was trying to figure out how to get 7 if formulas to give me a grand total.

 

[oldbrewer]

Re: Need help with SUM from multiple formulas (time difference calc)

		09:30	15:30	06:00	0.25
		09:45	16:00	06:15	0.26
		10:00	16:30	06:30	0.27
		10:15	17:00	06:45	0.28
		10:30	17:30	07:00	0.29	1.35
		2.08	3.44		1.35	32.5
		add all the start times and subtract from total of finish times
		working with numbers
		as demonstrated above

<colgroup><col width="64" span="9" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Endosmok]

Re: Need help with SUM from multiple formulas (time difference calc)

Ok... let me try to grasp this... your 3rd column is the time difference, correct? which equals the 32.5?
2.08 is total of start time, 3.44 total of end time? 1.35 is your subtraction. How does 1.35 correlate to 32.5? Once I see that I will understand it more.
Also, using your method... what if a start time is 1900 and end time is 0300? does it still work?

		09:30	15:30	06:00	0.25
		09:45	16:00	06:15	0.26
		10:00	16:30	06:30	0.27
		10:15	17:00	06:45	0.28
		10:30	17:30	07:00	0.29	1.35
		2.08	3.44		1.35	32.5
		add all the start times and subtract from total of finish times
		working with numbers
		as demonstrated above

<tbody>
</tbody>