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.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!