Pete Wright
Member
Hi Everybody!
I have a sheet with my working hours and I want to sum up one day's working time
The table is very simple:
A = Date
B = Start Time
C = End Time
D = Hours
E = Sum of Working Hours per Day
The Hours are calculated as follows:
=IF(OR(ISBLANK(B3),ISBLANK(C3)),"",IF(C3<B3,24+24*(C3-B3),24*(C3-B3)))
For row nr 3 => 24 * (C3-B3)
To keep things simple there are only 4 possible entries for the decimals:
X.00 X.25 X.50 X.75
But there can be more than just one row for a specific Date.
Example:
____A________D______E_
07/06/2018___3:00
07/06/2018___2:30___5,5
07/07/2018___6:00___6,0
07/08/2018___5:45___5,75
07/09/2018___4:00
07/09/2018___1:00___5,00
The problem is column E. I have no idea how to achieve this.
I once had a worksheet with things figured out, but I lost it on one of my many hard drives...
As far as I remember, I used the SUMIF function.
So I tried this:
=SUMIF($A$1:$A$6,"A1<>A2",$D$1:$D$6)
what I think it does:
compare if two consecutive cells have different dates and if they do, sum up all above
what Excel does:
Go to hell, I ain't sum up nothing here :-(
Does anybody know how to get the job done?
Besides:
It should only contain formulas available in Excel for Android, since I type the working hours on my mobile and print them out at the end of a month at home.
Many thanks in advance
I have a sheet with my working hours and I want to sum up one day's working time
The table is very simple:
A = Date
B = Start Time
C = End Time
D = Hours
E = Sum of Working Hours per Day
The Hours are calculated as follows:
=IF(OR(ISBLANK(B3),ISBLANK(C3)),"",IF(C3<B3,24+24*(C3-B3),24*(C3-B3)))
For row nr 3 => 24 * (C3-B3)
To keep things simple there are only 4 possible entries for the decimals:
X.00 X.25 X.50 X.75
But there can be more than just one row for a specific Date.
Example:
____A________D______E_
07/06/2018___3:00
07/06/2018___2:30___5,5
07/07/2018___6:00___6,0
07/08/2018___5:45___5,75
07/09/2018___4:00
07/09/2018___1:00___5,00
The problem is column E. I have no idea how to achieve this.
I once had a worksheet with things figured out, but I lost it on one of my many hard drives...
As far as I remember, I used the SUMIF function.
So I tried this:
=SUMIF($A$1:$A$6,"A1<>A2",$D$1:$D$6)
what I think it does:
compare if two consecutive cells have different dates and if they do, sum up all above
what Excel does:
Go to hell, I ain't sum up nothing here :-(
Does anybody know how to get the job done?
Besides:
It should only contain formulas available in Excel for Android, since I type the working hours on my mobile and print them out at the end of a month at home.
Many thanks in advance