Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculation problem
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? -- thank you, Lorie |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculation problem
On Sat, 21 Jul 2007 10:10:01 -0700, Lorie
wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculation problem
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. -- thank you, Lorie "Ron Rosenfeld" wrote: On Sat, 21 Jul 2007 10:10:01 -0700, Lorie wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculation problem
On Sat, 21 Jul 2007 11:20:01 -0700, Lorie
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FV Calculation problem | Excel Worksheet Functions | |||
Calculation problem - please help | Excel Discussion (Misc queries) | |||
Calculation problem? | Excel Discussion (Misc queries) | |||
Calculation Problem | Excel Discussion (Misc queries) | |||
Calculation Problem | Excel Discussion (Misc queries) |