Skip to Content
avatar image
Former Member

ACTUALS AND FORECAST labeling

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

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

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

    Add comment
    10|10000 characters needed characters exceeded

  • Aug 23, 2017 at 02:16 PM

    "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)

    Add comment
    10|10000 characters needed characters exceeded