I am recording Daily Hours worked in the following way: B1-A1=C1
Where A & B are the Start and Finish Times and C is the number of Hours Worked. These are in the Format 00:00:00 which gives a total of Daily Hours Worked in the Format 00:00:00
The problem: How do I add these up, to give a Weekly Total of Hours Worked.
I believe it's custom Format all cells as [h]:mm or if you want the seconds as well then [h]:mm:ss
Perhaps I should have said.
How do I add up the C Column?
The Formula that I have tried is =sum(C1:C7) but this does not produce the correct figure.
Maybe I should also have written hh:mm:ss rather that 00:00:00 but I had assumed that that was, sorta, obvious.
No the =sum(C1:C7) is okay, it's the hh should be [h]
wrong Format 00:00:00 which gives a total of Daily Hours Worked in the Format 00:00:00
should be Format [h]:mm which gives a total of Daily Hours Worked in the Format [h]:mm
or if you want the seconds as well [h]:mm:ss
I'm not an expert, it might be okay just to format the final total cell as [h]:mm I tried it like that and it works but I found it easier to format them all. The square brackets are what you want.
It just occurred to me, can I check that you are highlighting the cells and rightclicking and choosing 'Format Cells' and 'Number tab' in order to change cells to 'time' format.
Start time:~ 16:30:00 e.g. 4.30pm
Finish time:~ 03:25:00, e.g. 3.25am, the following morning
Total Hours:~ 10:55:00
As I type this I have realised that I will have to decimalise the "Hours Worked Column" (C), possibly in an hidden Column, before I can do this calculation.
Oh, B1-A1 goes over the midnight
you might be better to download a template http://office.microsoft.com/en-us/templates/CT010117277.aspx?av=ZXL
I got it, try this it works for me
A1,A2,A3,A4 custom format to dd/mm/yy hh:mm
B1,B2,B3,B4 custom format to dd/mm/yy hh:mm
C1,C2,C3,C4 custom format to hh:mm
(Total in C5) C5 custom format to [h]:mm
Unfortunately it's only a small part of a, much, much larger, Stats Sheet.
I was trying to get Weekly Totals as Daily Totals are not a reliable comparison, Year on Year that is, and mean little, as there are far too many variables.
This thread is now locked and can not be replied to.
OnePlus 5 review
See the work of famous artists playing with toys
iPad Pro 10.5in (2017) review
Comment faire une capture d’écran sur un Mac ?
© Copyright 2017 IDG UK. All Rights Reserved.