start counting if date is not in past

[Megatronixs]

Hi all,

I have maybe a very strange question. I need to calculate the time I have left to prepare a letter. It is possible that we get a letter asking for information as per a past date, or for a future date. When we receive the letter we register on the date we got it. If the letter is received today and they need the data for the date as per 31/12/2012, then I have 14 days for normal letter and 5 days for special letter. If we receive a letter for the date as per 30/06/2013, then it is a future date and I need to wait for that day to come and then I have 14 or 5 days left to produce it and send it out.
The problem I have is that I can see how many days I have left for a past date, but I can't see the ones for the future date in the sheet.
Can this be done?
I would like to use the sheet so I can see which letter should be produced first so I can have an order in them and avoid doing this manually (some times over 100 letters) as I will be able to filter on the column B and have the first one show up on top.

Thanks in advance,
megatronixs

P.S. see attached excel sheet.

[Melvinrobb]

Would this formula work in F2:

=MAX(0,(E2+D2)-TODAY())

[Megatronixs]

Hi Melvinrobb,

If I put the

=WORKDAY(A2;D2)+F2

Then I get wrong dates as there is something with the workdays. How do I get 18/07/2013 in C3?

Greetings,
megatronixs

[Melvinrobb]

I'm confused. What and where EXACTLY do you want displayed? Did the first formula I provide you with help? was it completely wrong

Then I get wrong dates as there is something with the workdays. How do I get 18/07/2013 in C3?

When you say C3, do you mean B2?
If so, would the following formula not work:

=WORKDAY(E2,D2)

[Megatronixs]

Hi, you are right (some how I got confused).
Please see attached excel file where the last row has the old formula (B5) and gives the correct date for the last day the letter can be finished. If I change it like the above formulas then it gives me a past day compared to the day received. I was thinking about a combination with the first formula. If the number is bigger then 0, use that formula, if 0, then use second formula.

Greetings,
megatronixs

[daddylonglegs]

Try using MAX with WORKDAY, e.g. this formula in B2 copied down

=WORKDAY(MAX(A2,E2),D2)

[Megatronixs]

Hi all,

Thank you for the great help. With both of your solutions I was able to solve it :-)

Greetings,
megatronix