Adding Minutes & Hours

[TheRook]

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.

 

[Bob Phillips]

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)

 

[TheRook]

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?

 

[Bob Phillips]

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)

 

[Bob Phillips]

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)

 

[TheRook]

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

 

[Bob Phillips]

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)