I'd like to use conditional formatting to set a cell's content to different text values based on whether the current date is less than or greater than the cell's date.
If cell value > %TODAY% Set cell content Active If cell value < %TODAY% Set cell content Completed
It seems that the only way to get the "less than" or "greater than" operators is to set the column type to "Date," but since I'm using an imported Excel file to populate the table, the PHPExcel library supposedly doesn't parse Excel dates correctly. Is there any fix or workaround for this situation?
I've tried the conditional formatting You're trying to use and it works just fine.
Now, since You've created a table by importing data from Excel, the thing You'd need to do before generating the table is to set the Date column's type to be Date, and select the Date input format. This is all done in the first step after selecting a file for upload.
When the table has been created, it should display the date normally and allow You to set conditional formatting. You can take a look at the attachments on how I created a table using an existing Excel file and set the date and conditional formatting like You described.
I think I found the cause of the problem: it doesn't work only if there are non-date strings in some of the cells of the date column. In this situation, is there any way to get the conditional formatting I've mentioned before to work or does it work only if all of the cells of a column contain dates?
Also, is there any way to filter a date column based on what the cell content is set to ("Active" or "Completed," for instance) as a result of conditional formatting?
does it work only if all of the cells of a column contain dates?
I believe all cells have to contain data in order for the conditional formatting to work.
And, unfortunately, You can't filter a date column based on the conditional formatting.
I would like to test both issues locally. If the Excel file doesn't contain any sensitive data, could You send it to me, so that I can recreate the table on my end? Then I'll be able to provide a better answer.
Could "filtering a column based on the conditional formatting" be something that might added in the future?
Anyway, here's the Excel file. Since the kind of filter mentioned above won't work, I don't think I'll use the conditional formatting but might use it for something else in the future.
We carefully follow customers requirements and try to implement most of them if they appear in requests from time to time, so we will add to our idea list and look into some possible solutions on plugin side in the future.
I've noticed that the format for those columns is different for some reason. Also, like You already guessed in one of Your previous replies, You have "active" in the third column, so it is being registered as a String, not Date. All values in the file have to be the same format for wpDataTables to recognize it successfully.
Also, even though the plugin inserts the values from the second column, where You have empty cells, I believe it is also being recognized as string because of those empty cells.
I'd like to use conditional formatting to set a cell's content to different text values based on whether the current date is less than or greater than the cell's date.
If cell value > %TODAY% Set cell content Active
If cell value < %TODAY% Set cell content Completed
It seems that the only way to get the "less than" or "greater than" operators is to set the column type to "Date," but since I'm using an imported Excel file to populate the table, the PHPExcel library supposedly doesn't parse Excel dates correctly. Is there any fix or workaround for this situation?
https://wpdatatables.com/documentation/column-features/date-columns/
Hello anenvatouser.
I've tried the conditional formatting You're trying to use and it works just fine.
Now, since You've created a table by importing data from Excel, the thing You'd need to do before generating the table is to set the Date column's type to be Date, and select the Date input format. This is all done in the first step after selecting a file for upload.
When the table has been created, it should display the date normally and allow You to set conditional formatting. You can take a look at the attachments on how I created a table using an existing Excel file and set the date and conditional formatting like You described.
Best regards.
Kind Regards,
Aleksandar Vuković
[email protected]
Rate my support
wpDataTables: FAQ | Facebook | Twitter | Instagram | Front-end and back-end demo | Docs
Amelia: FAQ | Facebook | Twitter | Instagram | Amelia demo sites | Docs | Discord Community
You can try wpDataTables add-ons before purchasing on these sandbox sites:
Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables
Sorry, I meant "linking" a table to an existing data source rather than "importing" a table. If an Excel file is used, it doesn't work.
I think I found the cause of the problem: it doesn't work only if there are non-date strings in some of the cells of the date column. In this situation, is there any way to get the conditional formatting I've mentioned before to work or does it work only if all of the cells of a column contain dates?
Also, is there any way to filter a date column based on what the cell content is set to ("Active" or "Completed," for instance) as a result of conditional formatting?
Hello anenvatouser.
About Your question:
does it work only if all of the cells of a column contain dates?
I believe all cells have to contain data in order for the conditional formatting to work.
And, unfortunately, You can't filter a date column based on the conditional formatting.
I would like to test both issues locally. If the Excel file doesn't contain any sensitive data, could You send it to me, so that I can recreate the table on my end? Then I'll be able to provide a better answer.
Best regards.
Kind Regards,
Aleksandar Vuković
[email protected]
Rate my support
wpDataTables: FAQ | Facebook | Twitter | Instagram | Front-end and back-end demo | Docs
Amelia: FAQ | Facebook | Twitter | Instagram | Amelia demo sites | Docs | Discord Community
You can try wpDataTables add-ons before purchasing on these sandbox sites:
Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables
Could "filtering a column based on the conditional formatting" be something that might added in the future?
Anyway, here's the Excel file. Since the kind of filter mentioned above won't work, I don't think I'll use the conditional formatting but might use it for something else in the future.
Hello again anenvatouser.
We carefully follow customers requirements and try to implement most of them if they appear in requests from time to time, so we will add to our idea list and look into some possible solutions on plugin side in the future.
I've noticed that the format for those columns is different for some reason. Also, like You already guessed in one of Your previous replies, You have "active" in the third column, so it is being registered as a String, not Date. All values in the file have to be the same format for wpDataTables to recognize it successfully.
Also, even though the plugin inserts the values from the second column, where You have empty cells, I believe it is also being recognized as string because of those empty cells.
Best regards.
Kind Regards,
Aleksandar Vuković
[email protected]
Rate my support
wpDataTables: FAQ | Facebook | Twitter | Instagram | Front-end and back-end demo | Docs
Amelia: FAQ | Facebook | Twitter | Instagram | Amelia demo sites | Docs | Discord Community
You can try wpDataTables add-ons before purchasing on these sandbox sites:
Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables