XL formula help required

[Kushi Krishnappa]

Hello Buddies,


I have come up some typical problem of preparing the planning/procurement sheet.


My procurement planning is based on seasonal medicinal herbs, when was preparing the sheet, I have stuck with from next coverage month to till next season starts, what is the gap in terms of months?


Basically I wanted to derive at how many I want to plan & procure the materials (considering seasonality).


Please help me with the formula to frame, it’s very important as if I miss any season, my complete planning will be shattered.

 

[NARAYANK991]

Hi ,

I am not clear on what you want the formulae to do.

Can you explain :

In which cells do you want formulae to be entered ?

What should the formula display ?

It would be ideal if you could work out a few examples in your workbook itself , so that it becomes clear.

Narayan

 

[Kushi Krishnappa]

Hi ,

I am not clear on what you want the formulae to do.

Can you explain :

In which cells do you want formulae to be entered ?

What should the formula display ?

It would be ideal if you could work out a few examples in your workbook itself , so that it becomes clear.

Narayan

Hello,

In AN3 i want the formula to be displayed.

Formula should be an out put of No. months procurement to be planned from the Coverage next month to till next seasons starts.

Take eg 1st line Makoy, with the available stock i have coverage till Mar 18, my procured month will start from Apr 18 till next season of the material is Mar 19

Above is manual calculation, i want formula to be framed for this.

Please help.

regards,
Kushi

 

[NARAYANK991]

Hi ,

See if this is correct. I have used a helper column so that you can verify the calculations.

Narayan

 

[Kushi Krishnappa]

Hi ,

See if this is correct. I have used a helper column so that you can verify the calculations.

Narayan

Sir,

Am little confused, if you see AM3 cell (Coverage month) for 1st line item is 30/03/18.

My requirement starts from 01/4/18 (next month of coverage month) till next season starts is Mar 19, Correct?

wanted to calculate B/w 01/04/18 - 01/03/19, how many months are deriving.

But if i see d updated file, AN3 cell is reflecting year 2019?... It should be 01/04/2018 (Next month of coverage month).

Please crack this issue!!!

regards,
Kushi

 

[NARAYANK991]

Hi ,

The column AN header label is To.

All dates in this column are dates starting from the next day after the dates shown in column AM.

You have said that the number of months should be from April 1 , 2018 till March 1 , 2019 ; this is exactly what is shown in column AO which is labelled Months.

I don't understand why a separate column should be dedicated to the day after the coverage date ends. This date is a part of the formula in column AO , which is :

=DATEDIF(EOMONTH(AM3,0)+1, AN3, "m")

where the highlighted +1 is the start date , while the date in AN3 is the end date.

Narayan