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

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

 

[Frank Kabel]

Hi
it would be helpful if you provide more detail
- some example data
- your espected results

 

[Tiffany]

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

 

[Frank Kabel]

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

 

[Tiffany]

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

 

[Frank Kabel]

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

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

 

[Tiffany]

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

 

[Frank Kabel]

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.

 

[Tiffany]

Thanks again so much, I don't know yet if it will work out the way w
need it to, But It SEEMS to... I have 10 minutes left until i have t
leave, so whats not done, has to wait.. .And i will take the heat o
Monday.

again Thanks for everything.:

 

[Frank Kabel]

Hi
you're welcome. If you still have problems on monday just come back to
this NG

 

[Tiffany]

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

 

[Frank Kabel]

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)