Help With Auto setting Fiscal Year based on Order Date

Coen

Good morning,

I seem to be having trouble with setting my Fiscal Year on a sheet. It was working but stopped. This is what I am working with:

I am importing a CSV file using Data Shuttle to import SKU information into an Import Result sheet.

I am attempting to set a column up to display the Fiscal Year based on the date Order Date that is imported.

The format of the order date as imported is: YYYY-MM-DD 00:00:00.

My current formula to figure out the FY is: =IF([Order Date]@row < DATE(2023, 5, 23), "2023", "2024"). - I get an #Invalid Operation Error.

I thought maybe the 00:00:00 was giving me the problem, so I created a new column and named it: Clean Order Date and used the following formula to remove the 00:00:00 from the date: =SUBSTITUTE([Order Date]@row, "00:00:00", "")

This resulted in an order date formatted like: YYYY-10-13. Unfortunately I still get an Invalid Operation error.

Any Guidance is greatly appreciated.

Paul Newcome

Because your order date is coming in as a text string, and the SUBSTITUTE function is still outputting a text string. Since neither are date values, comparing them to a DATE function is throwing the error.

You need to convert the text string into a date value before it can be used in comparison to another date value.

=DATE(VALUE(LEFT([Order Date]@row, 4)), VALUE(MID([Order Date]@row, 6, 2)), VALUE(MID([Order Date]@row, 9, 2)))