Help With Auto setting Fiscal Year based on Order Date
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.
Answers
-
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)))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!