Skip to Content
0

ACTUALS AND FORECAST labeling

Aug 18, 2017 at 04:14 PM

36

avatar image
Former Member

Hello Everyone,

I have a report and wish to label the columns as either ACTUALS or FORECAST

Firstly I determined the current period using the code that I "borrowed" for an online blog. It works giving me 2017.08 (in cell C1 for example) See code right below

=CONCATENATE(YEAR(TODAY()),”.”;IF(MONTH(TODAY())=1,”0″,IF(MONTH(TODAY())=2,”0″,IF(MONTH(TODAY())=3,”0″,IF(MONTH(TODAY())=4,”0″,IF(MONTH(TODAY())=5,”0″,IF(MONTH(TODAY())=6,”0″,IF(MONTH(TODAY())=7,”0″,IF(MONTH(TODAY())=8,”0″,IF(MONTH(TODAY())=9,”0″,))))))))),MONTH(TODAY()))

I then hardcoded the periods for comparison, by simply typing them in along the rows

C9 C10 C11

2017.01 2017.02 2017.03 etc.

I should just be able to use the formula =if(c1>=C9, "ACTUALS","FORECAST"). The problem however is that the always returns ACTUALS. Even when the comparison is false. In fact when i look at the formula it always returns true.

Why is this happening?

Lucy

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

avatar image
Former Member Aug 20, 2017 at 03:07 PM
0

Vadim!!! - My knight in dusty armor, surely you can shed light on this one/

Share
10 |10000 characters needed characters left characters exceeded
Vadim Kalinin Aug 23, 2017 at 02:16 PM
0

"I "borrowed" for an online blog" - never use code without understanding :)

Somebody without Excel knowledge provided this code!

Better code in C1:

=YEAR(TODAY())&"."&RIGHT("0"&MONTH(TODAY()),2)

And for the rest look on my screenshot (I have ";" instead of "," as an argument separator in excel)


exf.png (52.1 kB)
Share
10 |10000 characters needed characters left characters exceeded