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