HELP!need to determine price based on time to complete using Min. Hour. And days

  • Thread starter Thread starter Tiffany
  • Start date Start date
T

Tiffany

I need help ASAP.. I need to determine a price, based on the time to
complete a project. I plan on using the If statement, but i am not
able to get it to correctly determine the time, once it gets below 1
day. PLEASE HELP....
 
Hi
it would be helpful if you provide more detail :-)
- some example data
- your espected results
 
I guess that would help.. I am SO STRESSED over this...

Anyways.. to give an example...

Say i can produce 4 parts in a day, working 12 hours a day and i hav
500 parts to complete the project. I need to calculate the time it wil
take to complete the project (Which THINK i have it figured ou
correctly.)

THEN, based on that number, i need to be able to calculate the cost t
produce based on Cost per Day, Cost per Hour, and cost per minute.
THose amounts are all FIXED amounts.

The biggest problems i am having is 1, getting the IF statment correct
I was able to get the correct numbers for day and hour, but the
determining between hours and minutes in my time became a problem.
have changed the Format in the Time to produce Cell for Time, with th
correct amount of time, but the IF statement says Invalid when i ente
the following:

=IF(T16>1:00:00 ETC.... It does not like the number entered that way
I have about 3 hours to have this program COMPLETED.....
 
Hi
So lets try to remove some stress :-)
First some questions:
- You have different rates for days, hours and minutes (that is 60*rate
for a minute <> rate for a hour??)
- How did you exactly calculate the completion time (is the result
hours) -> please give a real-life example

Lets assume your value is in hours (e.g. it takes 16:20 hours to
complete the work -> 1 day, 4 hours and 20 minutes) and this value is
stored in cell A1 use the following to calculate your costs
1. costs for the days:
=INT((A1*24)/12)*day_rate
2. costs for the hours
=INT(MOD(A1*24,12))*hour_rate
3. costs for the minutes
=((MOD(A1*24,12))-INT(MOD(A1*24,12)))*60*minute_rate

If you like you can combine this to a large formula to calculate your
total costs:
=INT((A1*24)/12)*day_rate+INT(MOD(A1*24,12))*hour_rate+((MOD(A1*24,12))
-INT(MOD(A1*24,12)))*60*minute_rate

Come back if you have further questions
 
Frank said:
Hi
So lets try to remove some stress :-)
First some questions:
- You have different rates for days, hours and minutes (that is
60*rate
for a minute <> rate for a hour??)
- How did you exactly calculate the completion time (is the result
hours) -> please give a real-life example

I already have the TOTAL amount i need to use for each Days, Hours,
and Minutes.

=SUM(E20/(T4*60*T12))/2

Where E20=TOTAL PARTS for the project
T4= Parts per minute,
T12 = Hours worked each day

We had originally had the completion time in a decimal number (10.441
i believe is what it was)

I hope i gave you enough info.. I am sorry if i am confusing you,
because i think i confused myself in this one.. I swear, i do know
what i am talking about...lol Lunch has come and gone, without me
leaving my desk.... :confused:
Click to expand...
 
Hi
try the following to make my other formula work:
To calculate the total time in hours for your project use the following
formula
=(E20/(T4*60))/24
format this resulting cell with the custom format [hh]:mm to see how
many hours your project takes. I divided this value with 24 to convert
this number into Excel's time format (1 hour = 1/24 for Excel)

Lets say this value is stored in cell A1. you can use the following
formulas to calculate your costs:

1. costs for the days:
=INT((A1*24)/T12)*day_rate
2. costs for the hours
=INT(MOD(A1*24,T12))*hour_rate
3. costs for the minutes
=((MOD(A1*24,T12))-INT(MOD(A1*24,T12)))*60*minute_rate

just replace the rates with your specific cost rate. The addition of
these 3 formulas will give you the total costs.
hope this helps :-)


--
Regards
Frank Kabel
Frankfurt, Germany
Frank said:
Hi
So lets try to remove some stress :-)
First some questions:
- You have different rates for days, hours and minutes (that is
60*rate
for a minute <> rate for a hour??)
- How did you exactly calculate the completion time (is the result
hours) -> please give a real-life example

I already have the TOTAL amount i need to use for each Days, Hours,
and Minutes.

=SUM(E20/(T4*60*T12))/2

Where E20=TOTAL PARTS for the project
T4= Parts per minute,
T12 = Hours worked each day

We had originally had the completion time in a decimal number (10.441
i believe is what it was)

I hope i gave you enough info.. I am sorry if i am confusing you,
because i think i confused myself in this one.. I swear, i do know
what i am talking about...lol Lunch has come and gone, without me
leaving my desk.... :confused:
Click to expand...
Click to expand...
 
First, thanks so much for everything.. But, either I am way wrong, or w
are Thinking about 2 different things. For the cost to produce, We d
not have a RATE we have a Dollar Amount. So say 100/day 50/hour/10/mi
(just to simplify) are the fixed expenses. What i need to do is say i
takes 2 days, 1 hours and 10 minutes to complete,
The amount i would need to get would be 260.

I don't think i had made that very clear
 
Hi
sorry for confusing you (should have stated day_amount, etc.). Just use
these values in the formulas i provided to you. So try
1. costs for the days:
=INT((A1*24)/T12)*100
2. costs for the hours
=INT(MOD(A1*24,T12))*50
3. costs for the minutes
=((MOD(A1*24,T12))-INT(MOD(A1*24,T12)))*60*10

ccording to your example. Just replace my dummy 'rate variables' with
your respective dollar amount for hours/days/minutes.
 
Well, that formula is not giving us what we want it to. We are no
having a problem with getting a Time format to multiply is a Currenc
format (i assume its possible)

I am so ready to just give up on this program. I spent about 20 hour
on it now, and its STILL NOT WORKING... and here to think i was prett
good with excel... Guess I just can't cut it with this detail
 
Hi
have you formated the target cells ad 'Currency'. This may be the
problem.
If you like, you can email me your sheet and I'll have a look at it
(frank[dot]kabel[at]freenet[dot]de)
 
Back
Top