Rounding up or down
 

I column B I have a start time, in C an end time and in E I have the total
hours. At the end of the month I have a total for column E. If I enter 10:00
in B and 10:45 in C for 30 days, it says .8 for each of the 30 days in column
E which is what I want. The problem is at the end of the month it is not
adding all the .8's, it is adding 30 days * 45 minutes which is 22.5 rather
than 24.0.

Is there a formula that I can use to round this up or down.

Thanks

Rounding up or down
 

10:45 - 10:00 = 0:45

Converted to a decimal = 0.75

So, if you have 0.8 in the cells and 30 of those cells sum to 22.5 then the
0.8 is a FORMATTED value. The true underlying value of those cells is still
0.75.

So, to get the cells' true value to be 0.8:

=ROUND((C1-B1)*24/0.1,0)*0.1

Then a sum of D1:D30 will return 24.

Biff

"Kevin" wrote in message
...
I column B I have a start time, in C an end time and in E I have the total
hours. At the end of the month I have a total for column E. If I enter
10:00
in B and 10:45 in C for 30 days, it says .8 for each of the 30 days in
column
E which is what I want. The problem is at the end of the month it is not
adding all the .8's, it is adding 30 days * 45 minutes which is 22.5
rather
than 24.0.

Is there a formula that I can use to round this up or down.

Thanks

Rounding up or down
 

Thanks

Now if I have anouther function I would like to have in the same cell how
would I do that?

=ROUND((C5-B5)*24/0.1,0)*0.1 and =IF(B5="stat",8,(C5-B5)*24)-D5



"T. Valko" wrote:

10:45 - 10:00 = 0:45

Converted to a decimal = 0.75

So, if you have 0.8 in the cells and 30 of those cells sum to 22.5 then the
0.8 is a FORMATTED value. The true underlying value of those cells is still
0.75.

So, to get the cells' true value to be 0.8:

=ROUND((C1-B1)*24/0.1,0)*0.1

Then a sum of D1:D30 will return 24.

Biff

"Kevin" wrote in message
...
I column B I have a start time, in C an end time and in E I have the total
hours. At the end of the month I have a total for column E. If I enter
10:00
in B and 10:45 in C for 30 days, it says .8 for each of the 30 days in
column
E which is what I want. The problem is at the end of the month it is not
adding all the .8's, it is adding 30 days * 45 minutes which is 22.5
rather
than 24.0.

Is there a formula that I can use to round this up or down.

Thanks

Rounding up or down
 

=IF(B5="stat",8,ROUND((C5-B5)*24/0.1,0)*0.1)-D5 ?
You might also look at
=IF(B5="stat",8,MROUND((C5-B5)*24,0.1))-D5
if you have the Analysis ToolPak enabled.
--
David Biddulph

"Kevin" wrote in message
...
Thanks

Now if I have anouther function I would like to have in the same cell how
would I do that?

=ROUND((C5-B5)*24/0.1,0)*0.1 and =IF(B5="stat",8,(C5-B5)*24)-D5

"T. Valko" wrote:

10:45 - 10:00 = 0:45

Converted to a decimal = 0.75

So, if you have 0.8 in the cells and 30 of those cells sum to 22.5 then
the
0.8 is a FORMATTED value. The true underlying value of those cells is
still
0.75.

So, to get the cells' true value to be 0.8:

=ROUND((C1-B1)*24/0.1,0)*0.1

Then a sum of D1:D30 will return 24.

"Kevin" wrote in message
...
I column B I have a start time, in C an end time and in E I have the
total
hours. At the end of the month I have a total for column E. If I enter
10:00
in B and 10:45 in C for 30 days, it says .8 for each of the 30 days in
column
E which is what I want. The problem is at the end of the month it is
not
adding all the .8's, it is adding 30 days * 45 minutes which is 22.5
rather
than 24.0.

Is there a formula that I can use to round this up or down.

Rounding up or down
 

I think u have problem to sum of 30 days working houres. if it is :
1- Insert sum function for working days
2- If sum is not correct:
3- goto Format Cells, Number Page, Custom, in Type EditBox wite :
[h]:mm

"Kevin" wrote:

I column B I have a start time, in C an end time and in E I have the total
hours. At the end of the month I have a total for column E. If I enter 10:00
in B and 10:45 in C for 30 days, it says .8 for each of the 30 days in column
E which is what I want. The problem is at the end of the month it is not
adding all the .8's, it is adding 30 days * 45 minutes which is 22.5 rather
than 24.0.

Is there a formula that I can use to round this up or down.

Thanks