Chart widget not graphing dates correctly

VL0001

I am trying to graph the costs spent on projects per month in th dashboard using the chart widget. The problem is when the project goes into the next year instead of setting the first months spend ahead the last month in the last years spend it moves it to be next to the same month in the previous year as seen in the january months in the below screenshot showing 2024 & 2025 next to eachother.

I am guessing this starts at the report its pulling from that i am also unable to get the dates to align correctly as seen in the 2nd screenshot below.

I tried grouping by mm/yyyy in ascending order but it did not help.

Any input would be appreciated!

Kelly Moore

@VL0001

Try this. If there are not values in both the Month and Year helper columns, the formula cell will return a blank. If you don't want this, let me know.

=IF(AND([Year Helper]@row <> "", [Month Helper]@row <> ""), IF(LEN(VALUE([Month Helper]@row)) = 2, IFERROR([Year Helper]@row + "/" + [Month Helper]@row, ""), IFERROR([Year Helper]@row + "/0" + [Month Helper]@row, "")))

Kelly

Paul Newcome

@Kelly Moore Here's a trick I use to zero fill that is easily adaptable to any number of leading zeros.

=RIGHT("00" + MONTH([Date Column]@row), 2)

Just change the number of zeros and the number of characters for the RIGHT function to however many characters you want the final output to be.

Of course IFERRORs and whatnot can also be added in, but the above is the basic idea.

Kelly Moore

hey @VL0001

When faced with the exact problem, my work around was to create a helper column YYYY-MM. Placing the Year first will arrange the months chronologically and then you can sort the report based on this column. In my case, I used my column as my x-axis for the chart - you may be able to still use your primary column.

Kelly

VL0001

Did you create the helper column in the sheet or the report? I would guess sheet?

VL0001

I tried this and its actually worse.

Kelly Moore

@VL0001

My months were always two digit designations, eg, 01, 02. I'll take a guess at your current formula and will offer the adjustment. The IF statement decides if the Month is a one digit or two digit value by evaluating the length of the number portion. Leading zeroes are ignored in VALUE, thus the length of characters for any month less than October is one. That's how the IF works.

=IF(LEN(VALUE(LEFT([Payment Date]@row, 2))) = 2, YEAR([Payment Date]@row) + "/" + MONTH([Payment Date]@row), YEAR([Payment Date]@row) + "/0" + MONTH([Payment Date]@row))

Will this work for you?

Kelly

VL0001

I figured it had something to do with the lack of leading 0s. my if statement is quite a bit simpler.. I tried to copy and paste your formula, it worked except it was not setup to show nothing if there is no date in the helper columns or payment dates, what am i missing there?

=IFERROR([Year Helper]@row + "/" + [Month Helper]@row, "")

Kelly Moore

@VL0001

Try this. If there are not values in both the Month and Year helper columns, the formula cell will return a blank. If you don't want this, let me know.

=IF(AND([Year Helper]@row <> "", [Month Helper]@row <> ""), IF(LEN(VALUE([Month Helper]@row)) = 2, IFERROR([Year Helper]@row + "/" + [Month Helper]@row, ""), IFERROR([Year Helper]@row + "/0" + [Month Helper]@row, "")))

Kelly

VL0001

I see i was missing a ) when i tried to do it! I entered that in and then realized that the month and year helper columns are not blank when they dont have dates in them but they instead say #invalid data type. is there an easy way to write it so that if its not a date/number it doesnt show anything?

Paul Newcome

@Kelly Moore Here's a trick I use to zero fill that is easily adaptable to any number of leading zeros.

=RIGHT("00" + MONTH([Date Column]@row), 2)

Just change the number of zeros and the number of characters for the RIGHT function to however many characters you want the final output to be.

Of course IFERRORs and whatnot can also be added in, but the above is the basic idea.

VL0001

I ended up using the formula below since the helper columns had errors when they didnt have numers

=IF(OR(ISERROR([Month Helper]@row), ISERROR([Year Helper]@row)), "", [Year Helper]@row + "/" + IF(LEN([Month Helper]@row) = 1, "0" + [Month Helper]@row, [Month Helper]@row))