Using Forecast Sheet function off of pivot table

[bladz1454]

Does anyone know if this is possible? The Forecast Sheet function is very picky when it comes to only using valid data- in this case timeline MUST be in date format. This is bad news bears for pivots because if you group dates it converts them to text (strike out with Forecast) and if you ungroup and have multiple dates or gaps in days that also won't work (strike out with Forecast again).

Thoughts?

 

[sheetspread]

Convert the grouped dates to numbers

 

[bladz1454]

Not sure I follow, numbers are not valid time frame values for the forecast function.

 

[sheetspread]

You said they have to be in date format, dates are numbers. How do they look before and how do you want them to look after?

 

[bladz1454]

You said they have to be in date format, dates are numbers. How do they look before and how do you want them to look after?

For example, this is what the pivot table looks like when you have grouping by month & day selected:

[TABLE="width: 150"]
<colgroup><col></colgroup><tbody>[TR]
[TD] Jan [/TD]
[/TR]
[TR]
[TD] 2-Jan [/TD]
[/TR]
[TR]
[TD] 3-Jan [/TD]
[/TR]
[TR]
[TD] 4-Jan [/TD]
[/TR]
[TR]
[TD] 5-Jan [/TD]
[/TR]
[TR]
[TD] 8-Jan [/TD]
[/TR]
[TR]
[TD] 9-Jan [/TD]
[/TR]
[TR]
[TD] 10-Jan [/TD]
[/TR]
[TR]
[TD] 11-Jan [/TD]
[/TR]
[TR]
[TD] 12-Jan [/TD]
[/TR]
[TR]
[TD] 16-Jan [/TD]
[/TR]
[TR]
[TD] 17-Jan [/TD]
[/TR]
[TR]
[TD] 18-Jan [/TD]
[/TR]
[TR]
[TD] 19-Jan [/TD]
[/TR]
[TR]
[TD] 22-Jan [/TD]
[/TR]
[TR]
[TD] 23-Jan [/TD]
[/TR]
[TR]
[TD] 24-Jan [/TD]
[/TR]
[TR]
[TD] 25-Jan [/TD]
[/TR]
[TR]
[TD] 26-Jan [/TD]
[/TR]
[TR]
[TD] 29-Jan [/TD]
[/TR]
[TR]
[TD] 30-Jan [/TD]
[/TR]
[TR]
[TD] 31-Jan [/TD]
[/TR]
[TR]
[TD] Feb [/TD]
[/TR]
[TR]
[TD] 1-Feb [/TD]
[/TR]
[TR]
[TD] 2-Feb [/TD]
[/TR]
[TR]
[TD] 5-Feb [/TD]
[/TR]
[TR]
[TD] 6-Feb
[/TD]
[/TR]
</tbody>[/TABLE]

Not only does Forecast sheet want the dates in date format (ie. 1/1/18) but it wants it in a perfectly spaced format such as 1/1/18.. 2/1/18.. 3/31/18..

That being said, I might just group by month but even then the darn format in the pivot table is still in text format instead of date.

 

[sheetspread]

I only have Excel 2010, but you should be able to format those dates as numbers instead of text, either in place or a separate range. Otherwise I can give you a formula that will convert it.

 

[bladz1454]

I only have Excel 2010, but you should be able to format those dates as numbers instead of text, either in place or a separate range. Otherwise I can give you a formula that will convert it.

No worries. Yea, I think Forecast Sheet was added in 2016. I can rip the pivot data, manipulate the date information, and then accommodate the date gaps eventually letting me use the Forecast function - was just hoping someone had a faster method.

I still find it hard to believe that you need near 'perfect' data sets to use that function... such a shame.