Calculation problem

[Lorie]

Start time and stop time are entered all month long to show minutes worked on
a project

Now it's the end of the month, we need to pivot our reports to show units
work on a project.

1 min to 7.5 minutes = .5 units - then up to 15 minutes = 1 full unit.

We can't round as we go along during the month.
It has to be after each project is totaled up at the end of the month
Only then can we round

Any Ideas?

 

[Ron Rosenfeld]

Start time and stop time are entered all month long to show minutes worked on
a project

Now it's the end of the month, we need to pivot our reports to show units
work on a project.

1 min to 7.5 minutes = .5 units - then up to 15 minutes = 1 full unit.

We can't round as we go along during the month.
It has to be after each project is totaled up at the end of the month
Only then can we round

Any Ideas?

=CEILING(sum_of_minutes_worked*1440/15,0.5)

Multiply the Excel time value by 1440 to get the actual number of minutes;
divide by 15 to get the number of "units", then round it up to the closest 0.5


--ron

 

[Lorie]

I already use this formula = (C4-B4)*1440 so i am showing minutes

but when I pivot the report i get the minutes to show up one time for each
project,

but when i pay out, I pay by the units for each project.

So what i want to do is pivot the report, and end up showing the minutes,
and the minutes converted to units so it will speed up the billing process

Say i worked 533 minutes on a project
i would like it to show me that i worked 35-1/2 units......

=CEILING(D4*1440/15,0.5) This didn't work...
=CEILING(D12/15,0.5) nor this...
the answer should be 35/1/2 units (9 hours)

but the formula gives me 36, which is more than i can bill for

I really appricate any help.

 

[Ron Rosenfeld]

I already use this formula = (C4-B4)*1440 so i am showing minutes

but when I pivot the report i get the minutes to show up one time for each
project,

but when i pay out, I pay by the units for each project.

So what i want to do is pivot the report, and end up showing the minutes,
and the minutes converted to units so it will speed up the billing process

Say i worked 533 minutes on a project
i would like it to show me that i worked 35-1/2 units......

=CEILING(D4*1440/15,0.5) This didn't work...
=CEILING(D12/15,0.5) nor this...
the answer should be 35/1/2 units (9 hours)

but the formula gives me 36, which is more than i can bill for

I really appricate any help.

Well, then, you need to rethink the specifications you set out in your initial
post:

1 min to 7.5 minutes = .5 units - then up to 15 minutes = 1 full unit.

533 minutes is

35 fifteen minute units
PLUS
8 additional minutes

According to your specification, since 8 is more than 7.5, it should be
interpreted as one (1) full unit.


--ron