Generating New Date

Hi All,

I have a question reference excel generating a new date for a stock control system i use at work. Basically i used to look at recent production runs and work out a new 'stock run out' date manually based on various information. I've now changed it to a ROP based system where i just load in stock information via a CSV file and then use lookup tables - so now i have current stock held and the amount of days stock. What i would like to do is use excel to generate my new stock run out date for me using the current days date plus the amount of days stock = new date, the twist is we do not work weekends so it needs to skip saturdays and sundays.

so for example if i have 21 days stock of a given item, i would simply ask excel to add 21 days to todays date and thus generate a new date, but i do not know how to tell it to skip the weekends...

hope all this makes sense, any help much appreciate

-Mark

Hi, and welcome to the board

Use the WORKDAYS function which will allow to add days excluding weekends and holidays.
You need the Analysis Toolpak installed

Many thanks for your quick response

I've installed the Analysis pack, and can sort of see how the Workdays function works, although im still having difficulty gettin it to return a date for me?
Basically on my worksheet i have todays date as Today() e.g 17-Mar
I then have a column S/O Date which is where i want the new vaule generated,
lastly i have current stock column which generates number of days stock as a number e.g. 6

So if i currently type in the S/O Cell "=Today() + S/O Column" then it will return 23rd March (obviously this should be 25th March),
i then tried =(Workday(Today() + S/O Column)) and it returned a #n/a?

any more help appreciated
thanks
mark

Hi Mark,
maybe you can post a small sample of your data ?

I have now actually managed to figure it out and its all working well :-) apart from one part, which is when i now resort the data so i get the earliest date first, part of the workday formula changes and messes up the date :-( do you know how i can lock part of the formula so when i resort the information it always refers to the same cell??

thanks in advance!

-Mark

Referring to a same cell is what is called an "absolute reference"
It is obtained by adding the "$" sign before the cell column reference and/or the row reference ( f.i.$A$1 refernce will never change if you put the formula in antother cell)
Have a look at XL's help for more info