Time Calculation

[Paul Black]

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

 

[Sean Timmons]

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]

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

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.

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 -

 

[muddan madhu]

check it here

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

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

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.

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 -

 

[Paul Black]

Brilliant, thank you both.

All the Best.
Paul

check it here

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

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

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