# Date calculation based on data in a spreadsheet to identify start date for material

1. ## Date calculation based on data in a spreadsheet to identify start date for material

I have a spreadsheet that has a row range showing inventory depletion. The headers to these rows have a date. I also have a cell with a number in that represents how many days it takes to manufacture a lot of material. I need to identify when inventory turns negative in the first range, then look at the date in the header of that column, then calculate the start date going backwards from that date based on the number in the cell I have identified is the manufacturing time. No idea how to write this?

2. ## Re: MS Excel Formula Problem

HI Ikench,

Suggest you to change the thread title to reflect the query being asked ... see the forum rules:-

http://www.excelforum.com/forum-rule...rum-rules.html

Regards,
DILIPandey

<click on below 'star' if this helps>

3. ## Re: MS Excel Formula Problem

Hope this is better, sorry.

4. ## Re: Date calculation based on data in a spreadsheet to identify start date for material

Hi Ikench,

Thanks for updating the thread title...

I believe you can use the good mix of the formulas like Offset/ Match / Workday etc.. would suggest you to upload the sample workbook. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

5. ## Re: Date calculation based on data in a spreadsheet to identify start date for material

OK, I worked it out, but had to break it down into 3 cells.

Cell 1 (AA8) was used to calculate the quantity closest to 0. =INDEX(K8:V8,MATCH(MIN(ABS(K8:V8-AA\$4)),ABS(K8:V8-AA\$4),0))
Cell 2 (AB8) was used to calculate the date at the top of the column closest to 0. =INDEX(\$K\$7:\$V\$7,MATCH(AA8,INDEX(\$K\$8:\$V\$81,MATCH(I8,I\$8:I\$81,0),0),0))
Cell 3 (AC8) was used to count back days from AB8 based on a given lead time. =SUM(AB8-D\$2)

Probably an easier way but this is how I got it, and it works.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1