calculate lead time resulting in dates

[ryuugga]

Hi,

I have an excel on which i need to calculate to know when i will need to purchase the materials for the production for a certain Month.

For example, for the month of June, before the start of the month i need to know when i will need to purchase the materials, in date format, but if the date will hit on Saturday or Sunday, the date will move back on Friday.

Hope to hear on expert how to do this.

Thanks
Rodel

 

[MikeWx]

Hi Rodel,

Welcome to the MrExcel forum.

My formula below assumes you use the same amount of material each day, it is used every day (including Saturday and Sunday) and that you the know amount currently on hand. In my example, the remaining amount reaches 20 on Saturday, 6/9, so the reorder date is Friday, 6/8.

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px"><COL style="WIDTH: 68px"><COL style="WIDTH: 68px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 100px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 36px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Today</TD><TD>Current
Inventory</TD><TD>Daily
Usage</TD><TD>Reorder
Amount</TD><TD>Reorder
Date</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">5/25/2012</TD><TD style="TEXT-ALIGN: right">127</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">20</TD><TD style="TEXT-ALIGN: right">6/8/2012</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>E2</TD><TD>=A2+ROUNDUP((B2-D2)/C2,0)-CHOOSE(WEEKDAY(A2+ROUNDUP((B2-D2)/C2,0),2),0,0,0,0,0,1,2)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


Mike

 

[ryuugga]

Hi Mike,

Thank you very much for showing me sample on how to move the desired date from Saturday or Sunday.

Though my problem is not the same as what you showed me, It shows the same concept.

Thank you very much it really helps a lot.

Cheers.

Rodel