Adding Minutes & Hours

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a spreadsheet that contains all the parts that me manufacture and the
process times for each operation to make these parts. The process times are
in minutes per piece.

What I am wanting to do is calulate the start day for each operation by
deducting the process times from an end date (excluding non working days, we
currently work 24hs 5 days per week).

For example if we are making 100 of part no. 123ABC it would caculate as
follows:
Process times:
Operation 1 = 5 mins
Operation 2 = 10 mins
Operation 3 = 15 mins
Operation 4 = 60mins
End date = 31/10/06

Op 4 ~ 31/10/06 - (60mins x 100 = 4d 04h 00mins) = start date 24/10/06
Op 3 ~ 24/10/06 - (15mins x 100 = 1d 01h 00mins) = start date 20/10/06
Op 2 ~ 20/10/06 - (10mins x 100 = 0d 16h 40mins) = start date 19/10/06
Op 4 ~ 19/10/06 - (05mins x 100 = 0d 08h 20mins) = start date 18/10/06

I currently have a spreadsheet set up that calculates starts dates for a
differnt department but the process time are in days so it is quite easy.

If this is difficult to express in words I can forward my email address so
the you can send me an example that I can manipulate.

Thanks.
 
Once you have integral number of days, add that to the start date using

=WORKDAY(start_date,num_days)


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Sorry if I am being a little think but how do I get to the integral number of
days if the formula results in minutes?
 
Say the number of minutes is in A1, then use

=INT(A1/60/24)

60 to change to hours, 24 to change to days

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
BTW, the WORKDAY function is part of the Analysis Toolpak, so you will need
to check that is installed in Tools>Addins.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Thanks, I have now loaded the add-ins.

Just 1 more question (For now)

If the total mins = 2400 will the INT calculate this as 1 day or 2 days?

If one is ther anyway it can round it up, if it is over 1 full day to the
next full day?

regards
 
Ian,

INT will truncate it, so 2400 will go to 1 day.

If you want to round, use

=ROUND(A1/60/24,0)

if you want to round up, use

=ROUNDUP(A1/60/24,0)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Back
Top