SOLVED

Conditional Formatting isn't showing until the cell is interacted with

%3CLINGO-SUB%20id%3D%22lingo-sub-2098340%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20isn't%20showing%20until%20the%20cell%20is%20interacted%20with%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2098340%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20using%20Power%20Automate%20(Microsoft%20Flow)%20to%20create%20an%20Excel%20spreadsheet%20and%20email%20it%20to%20the%20user%20-%20flow%20is%20working%20all%20fine.%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20template%20which%20is%20updated%20in%20the%20flow%20has%20icon%20conditional%20formatting%20on%20the%20date%20columns%20so%20they%20should%20show%20as%20red%2Fyellow%2Fgreen%20depending%20on%20if%20they%20are%20overdue%2Fcoming%20up%20for%20renewal%2Fin%20date%2C%20see%20screenshot%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22formattingrule%20exel.png%22%20style%3D%22width%3A%20574px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F249547iB0C1529FD93BB90C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22formattingrule%20exel.png%22%20alt%3D%22formattingrule%20exel.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Ehowever%2C%20when%20the%20file%20is%20emailed%20it%20does%20not%20show%20the%20icons%2C%20instead%20showing%20the%20date%20itself%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Excel%20error.png%22%20style%3D%22width%3A%20770px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F249548iDB4B9F669FD1AD45%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Excel%20error.png%22%20alt%3D%22Excel%20error.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EHowever%2C%20if%20the%20user%20double%20clicks%20on%20the%20cell%20and%20hits%20enter%2C%20it%20then%20decides%20to%20show%20the%20formatting%20as%20it%20should%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22formattingcorrectexcel.png%22%20style%3D%22width%3A%20566px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F249549iAB8372CB31EF1ECB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22formattingcorrectexcel.png%22%20alt%3D%22formattingcorrectexcel.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20cannot%20for%20the%20life%20of%20me%20figure%20out%20why%20this%20is%20the%20case.%20To%20make%20matters%20more%20annoying%2C%20I%20have%20another%20spreadsheet%20(a%20previous%20iteration%20of%20this%20same%20project%2C%20but%20it%20was%20so%20old%20it%20was%20easier%20to%20recreate%20rather%20than%20edit)%20which%20works%20fine%2C%20and%20when%20the%20file%20is%20updated%20it%20shows%20the%20formatting%20straight%20away.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20not%20too%20sure%20if%20the%20error%20here%20is%20on%20the%20Excel%20end%20or%20the%20Flow%20end%20-%20I'm%20assuming%20excel%20because%20otherwise%20the%20flow%20is%20succeeding%20and%20this%20error%20is%20the%20same%20both%20on%20the%20emailed%20copy%20and%20the%20version%20which%20is%20saved%20to%20SharePoint%20as%20part%20of%20the%20flow.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20anyone%20have%20any%20idea%20on%20a%20fix%3F%20It's%20not%20a%20huge%20issue%20overall%20but%20does%20make%20the%20simple%20usability%20of%20the%20icon%20formatting%20a%20bit%20redundant%20if%20the%20user%20has%20to%20manually%20click%20on%20each%20cell%20to%20get%20the%20simple%20view.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2098340%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2098595%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20isn't%20showing%20until%20the%20cell%20is%20interacted%20with%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2098595%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F758580%22%20target%3D%22_blank%22%3E%40misce%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELooks%20like%20on%20updating%20the%20sheet%20Flow%20returns%20text%20instead%20of%20date%2C%20if%20user%20re-enter%20it%20is%20converted%20to%20date%20(actually%20number)%20and%20rule%20works.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMicrosoft%20changed%20about%20a%20week%20ago%20how%20date%20from%20Excel%20sheet%20is%20recognized%20by%20Flow%20(text%20in%20ISO%20format%20instead%20of%20date).%20Perhaps%20that's%20related%20change.%20However%2C%20few%20days%20ago%20reverted%20change%20back%20and%20new%20version%20shall%20be%20introduced%20soon.%20That's%20somewhere%20on%20support%20page.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20only%20the%20guess%2C%20I%20don't%20know%20for%20sure.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I am using Power Automate (Microsoft Flow) to create an Excel spreadsheet and email it to the user - flow is working all fine.

The template which is updated in the flow has icon conditional formatting on the date columns so they should show as red/yellow/green depending on if they are overdue/coming up for renewal/in date, see screenshot:

formattingrule exel.png

however, when the file is emailed it does not show the icons, instead showing the date itself:

Excel error.png

However, if the user double clicks on the cell and hits enter, it then decides to show the formatting as it should:

formattingcorrectexcel.png

 

I cannot for the life of me figure out why this is the case. To make matters more annoying, I have another spreadsheet (a previous iteration of this same project, but it was so old it was easier to recreate rather than edit) which works fine, and when the file is updated it shows the formatting straight away.

 

I'm not too sure if the error here is on the Excel end or the Flow end - I'm assuming excel because otherwise the flow is succeeding and this error is the same both on the emailed copy and the version which is saved to SharePoint as part of the flow.

 

Does anyone have any idea on a fix? It's not a huge issue overall but does make the simple usability of the icon formatting a bit redundant if the user has to manually click on each cell to get the simple view.

 

Thanks!

2 Replies
Best Response confirmed by misce (New Contributor)
Solution

@misce 

Looks like on updating the sheet Flow returns text instead of date, if user re-enter it is converted to date (actually number) and rule works.

 

Microsoft changed about a week ago how date from Excel sheet is recognized by Flow (text in ISO format instead of date). Perhaps that's related change. However, few days ago reverted change back and new version shall be introduced soon. That's somewhere on support page.

 

That's only the guess, I don't know for sure.

@Sergei Baklan  Ah that would certainly make sense considering the old version worked. Thank you, I will hold tight for the update and fingers crossed that fixes my problem!