Time Calculation

P

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
 
S

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

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.



Paul Black said:
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 -
 
M

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.
Paul Black said:
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 -
 
P

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 -
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top