Hi everyone,

I have a column of hours and minutes like 19:55, 07:12, 20:12 etc. I
have also managed to add up the total number of hours and minutes for
each week. I have used a custom format of [hh]:mm which works well.
Now, say the weekly working hours are 40, i would like to take the
total weekly hours and minutes they actuially worked and subtract what
hours they should have worked, i.e. 40.
So the formula would be something like ...

total hours and minutes actually worked - 40

Thanks in Advance.
All the Best
Paul

Excel treats time as numbers. So, 1 day = 1. 1 hour = 1/24.

40 hours = 40/24.

So, sum of times - (40/24) = answer. format as hours:mins.

"Paul Black" wrote:

Hi everyone,

I have a column of hours and minutes like 19:55, 07:12, 20:12 etc. I
have also managed to add up the total number of hours and minutes for
each week. I have used a custom format of [hh]:mm which works well.
Now, say the weekly working hours are 40, i would like to take the
total weekly hours and minutes they actuially worked and subtract what
hours they should have worked, i.e. 40.
So the formula would be something like ...

total hours and minutes actually worked - 40

Thanks in Advance.
All the Best
Paul

That works great, but if the actual hours worked is less than what
they should have worked it gives an error.
Ideally i would like it to show a + or - in front of it as well.

Thanks.
All the Best.
Paul

On Oct 2, 4:21*pm, Sean Timmons
wrote:
Excel treats time as numbers. So, 1 day = 1. 1 hour = 1/24.

40 hours = 40/24.

So, sum of times - (40/24) = answer. format as hours:mins.



"Paul Black" wrote:
Hi everyone,

I have a column of hours and minutes like 19:55, 07:12, 20:12 etc. I
have also managed to add up the total number of hours and minutes for
each week. I have used a custom format of [hh]:mm which works well.
Now, say the weekly working hours are 40, i would like to take the
total weekly hours and minutes they actuially worked and subtract what
hours they should have worked, i.e. 40.
So the formula would be something like ...

total hours and minutes actually worked - 40

Thanks in Advance.
All the Best
Paul- Hide quoted text -

- Show quoted text -

check it here

http://www.j-walk.com/ss/excel/usertips/tip051.htm


On Oct 2, 8:29*pm, Paul Black wrote:
That works great, but if the actual hours worked is less than what
they should have worked it gives an error.
Ideally i would like it to show a + or - in front of it as well.

Thanks.
All the Best.
Paul

On Oct 2, 4:21*pm, Sean Timmons



wrote:
Excel treats time as numbers. So, 1 day = 1. 1 hour = 1/24.

40 hours = 40/24.

So, sum of times - (40/24) = answer. format as hours:mins.

"Paul Black" wrote:
Hi everyone,

I have a column of hours and minutes like 19:55, 07:12, 20:12 etc. I
have also managed to add up the total number of hours and minutes for
each week. I have used a custom format of [hh]:mm which works well.
Now, say the weekly working hours are 40, i would like to take the
total weekly hours and minutes they actuially worked and subtract what
hours they should have worked, i.e. 40.
So the formula would be something like ...

total hours and minutes actually worked - 40

Thanks in Advance.
All the Best
Paul- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Brilliant, thank you both.

All the Best.
Paul

On Oct 2, 4:41*pm, muddan madhu wrote:
check it here

http://www.j-walk.com/ss/excel/usertips/tip051.htm

On Oct 2, 8:29*pm, Paul Black wrote:



That works great, but if the actual hours worked is less than what
they should have worked it gives an error.
Ideally i would like it to show a + or - in front of it as well.

Thanks.
All the Best.
Paul

On Oct 2, 4:21*pm, Sean Timmons

wrote:
Excel treats time as numbers. So, 1 day = 1. 1 hour = 1/24.

40 hours = 40/24.

So, sum of times - (40/24) = answer. format as hours:mins.

"Paul Black" wrote:
Hi everyone,

I have a column of hours and minutes like 19:55, 07:12, 20:12 etc. I
have also managed to add up the total number of hours and minutes for
each week. I have used a custom format of [hh]:mm which works well.
Now, say the weekly working hours are 40, i would like to take the
total weekly hours and minutes they actuially worked and subtract what
hours they should have worked, i.e. 40.
So the formula would be something like ...

total hours and minutes actually worked - 40

Thanks in Advance.
All the Best
Paul- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -