# Stop forecast formula



## Erkjerk (Jan 2, 2023)

Hello,

Just seeing if I can get a little help.  So I have a forecast formula =C2/DAY(TODAY())*31  The problem is it doesn't stop at the end of the month at the 31st, it will just keep calculating so the forecast will show some ridiculous number the day after the 31st.  I hope this makes sense.


----------



## Skybluekid (Jan 2, 2023)

How about 
=IF(DAY(TODAY())>31,"",C2/DAY(TODAY())*31)

Hope this helps


----------



## Erkjerk (Jan 2, 2023)

Looks like it is still pulling the super large number.  I am thinking it could be cause of the (today) formula or something?


----------



## Skybluekid (Jan 2, 2023)

Is this designed to stop calculation when then end of the month comes? If so, you could use:

=IF(TODAY()>EOMOTH(TODAY(),0),"",........


----------



## Fluff (Jan 2, 2023)

Today will never be greater than the end of this month.


----------



## Erkjerk (Jan 2, 2023)

Fluff said:


> Today will never be greater than the end of this month.


Good point lol


----------



## Erkjerk (Jan 3, 2023)

Skybluekid said:


> =IF(TODAY()>EOMOTH(TODAY(),0),"",........



I feel stupid, what is the ..... I appreciate the help!


----------



## Skybluekid (Jan 4, 2023)

Very good point @Fluff


----------



## Skybluekid (Jan 4, 2023)

Erkjerk said:


> Hello,
> 
> Just seeing if I can get a little help.  So I have a forecast formula =C2/DAY(TODAY())*31  The problem is it doesn't stop at the end of the month at the 31st, it will just keep calculating so the forecast will show some ridiculous number the day after the 31st.  I hope this makes sense.


Looking at this with fresh eyes, is the 31 the number of days of the month? Does this change to correspond to the number of days for a given month?


----------



## Erkjerk (Jan 4, 2023)

Skybluekid said:


> Looking at this with fresh eyes, is the 31 the number of days of the month? Does this change to correspond to the number of days for a given month?


That is correct


----------



## Erkjerk (Jan 2, 2023)

Hello,

Just seeing if I can get a little help.  So I have a forecast formula =C2/DAY(TODAY())*31  The problem is it doesn't stop at the end of the month at the 31st, it will just keep calculating so the forecast will show some ridiculous number the day after the 31st.  I hope this makes sense.


----------



## Skybluekid (Jan 4, 2023)

The below will correct the month issue:
Book1CDEF23013140228533164307531863097311083111930121031131130141231Sheet1Cell FormulasRangeFormulaC3C3=C1/DAY(TODAY())*(VLOOKUP(MONTH(TODAY()),$E$3:$F$14,2,0))C4C4=C2/DAY(TODAY())*(LOOKUP(MONTH(TODAY()),{1,2,3,4,5,6,7,8,9,10,11,12},{31,28,31,30,31,30,31,31,30,31,30,31}))F4F4=DAYS(EOMONTH(DATEVALUE("01/02/"&YEAR(TODAY())),0),DATEVALUE("01/02/"&YEAR(TODAY())))+1

There two ways.  
1)The VLOOKUP will require a table of month numbers and days of the month to be located somewhere in the workbook(February is a calcuation as some years will be 29 days, the formula will do this automatically.

2) The Lookup is self contained as the look up value is month number today and this will bring back the number of days.  However, you will have to manually check February every to see if the days are correct.


----------



## Erkjerk (Saturday at 6:00 PM)

Awesome, thank you.  I will use the vlookup.  I am assuming I would have to change the formula though because I make a new sheet at the bottom for each separate month, I don't overwrite the current sheet.  So at the bottom of the excel workbook, first tab is Jan 2nd tab is Feb, 3rd is Mar, etc.  Thank you.


----------



## Skybluekid (Saturday at 6:05 PM)

No really, you can put the months on a separate sheet to the months and then reference the VLookup to that sheet.


----------



## Erkjerk (Saturday at 6:40 PM)

Skybluekid said:


> No really, you can put the months on a separate sheet to the months and then reference the VLookup to that sheet.


Awesome, I added the formula. I appreciate the help.


----------



## Skybluekid (Sunday at 3:02 AM)

You very welcome


----------



## Erkjerk (Sunday at 12:26 PM)

Just one more question lol. Is there a reason it is pulling from E3 and F14 rather then E3 and F3 for January?


----------



## Skybluekid (Sunday at 1:18 PM)

With Vookup, you need to specify the lookup value(month today, which will be 1), the range where the data is held(E3:F14),which column, within the range, where the data is to be returned, in this case column 2, finally whether want an exact or approximate match, in this case exact.

Have a look at this Video, minute 49, this will explain how VLOOKUP works


----------



## Erkjerk (Sunday at 1:40 PM)

Okay cool thanks


----------



## Skybluekid (Sunday at 2:03 PM)

No problem


----------

