SOLVED

Working and non working days

%3CLINGO-SUB%20id%3D%22lingo-sub-1271649%22%20slang%3D%22en-US%22%3EWorking%20and%20non%20working%20days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1271649%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%20values%20from%20cell%20E4%20to%20NF73%20and%20corresponding%20percentage%20change%20from%20cell%20NJ4%20to%20ABJ73.%20The%20purpose%20of%20analysis%20is%20to%20return%20a%20%25%20difference%20in%20current%20date%20as%20compared%20to%20immediately%20previous%20day%20and%20if%20there%20is%20no%20value%20in%20previous%20day%20then%20to%20take%20value%20from%20immediate%20last%20date%20which%20has%20value%20in%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThus%2C%20if%20value%20in%20current%20and%20previous%20dates%20are%20same%20then%20%25%20change%20will%20be%200%25.%20There%20is%20conditional%20formatting%20of%20hiding%200%25%20return%20values%20from%20cell%20NJ4%20to%20ABJ73.%20And%20now%20the%20problem%20starts.%20I%20cannot%20analyse%20it%20properly%20as%20I%20cannot%20differentiate%200%25%20return%20between%20a%20working%20day%20and%20a%20non%20working%20day.%20By%20working%20day%2C%20I%20mean%20to%20say%20cell%20that%20has%20value%20in%20column%20A%20and%20B.%20When%20there%20is%20no%20value%20in%20column%20A%20and%20B%2C%20it%20will%20be%20a%20non%20working%20day%20and%20it%20would%20have%20taken%20value%20from%20immediate%20preceding%20date%20that%20has%20a%20value.%20For%20example%2C%2004%2F01%2F1951%20and%2005%2F01%2F1951%20both%20cells%20have%20a%20value%20of%2020.87%20in%20cell%20B255%20and%20B256.%20And%20thus%2C%20cell%20NM5%20must%20show%200%25%20as%20it%20is%20an%20working%20day.%20Continuing%20it%20further%2C%2006%2F01%2F1951%20and%2007%2F01%2F1951%20has%20no%20value%20and%20so%20their%20value%20will%20be%2020.87%20i.e.%20value%20from%20immediate%20preceding%20date.%20And%20return%20for%20NN5%20and%20NO5%20will%20also%20be%200%25%20but%20as%20it%20is%20a%20non%20working%20day%2C%20its%20value%20should%20not%20be%20shown.%20So%2C%20the%20solution%20should%20show%200%25%20return%20if%20its%20a%20working%20day%20and%20not%20to%20show%200%25%20if%20its%20a%20non%20working%20day.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20will%20appreciate%20any%20questions%20to%20be%20clarified.%20Also%2C%20any%20alternate%20solutions%20are%20also%20highly%20welcomed.%20The%20spreadsheet%20is%20attached%20herewith.%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-1271649%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1272511%22%20slang%3D%22en-US%22%3ERe%3A%20Working%20and%20non%20working%20days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1272511%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F577386%22%20target%3D%22_blank%22%3E%40Dharmendra_Bharwad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENot%20to%20show%20days-off%20the%20formula%20for%20the%20rule%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20455px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F181329iF47ED1254BCF0786%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ecould%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DISERROR(MATCH(DATE(%24AK4%2CMONTH(AM%242)%2CDAY(AM%242))%2C%24A%3A%24A%2C0))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1273638%22%20slang%3D%22en-US%22%3ERe%3A%20Working%20and%20non%20working%20days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1273638%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20alot%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E.%20The%20formula%20and%20rule%20both%20works%20very%20accurately.%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20again%20for%20repeated%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1274452%22%20slang%3D%22en-US%22%3ERe%3A%20Working%20and%20non%20working%20days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1274452%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F577386%22%20target%3D%22_blank%22%3E%40Dharmendra_Bharwad%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%2C%20glad%20to%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

Hi,

 

There are values from cell E4 to NF73 and corresponding percentage change from cell NJ4 to ABJ73. The purpose of analysis is to return a % difference in current date as compared to immediately previous day and if there is no value in previous day then to take value from immediate last date which has value in it.

 

Thus, if value in current and previous dates are same then % change will be 0%. There is conditional formatting of hiding 0% return values from cell NJ4 to ABJ73. And now the problem starts. I cannot analyse it properly as I cannot differentiate 0% return between a working day and a non working day. By working day, I mean to say cell that has value in column A and B. When there is no value in column A and B, it will be a non working day and it would have taken value from immediate preceding date that has a value. For example, 04/01/1951 and 05/01/1951 both cells have a value of 20.87 in cell B255 and B256. And thus, cell NM5 must show 0% as it is an working day. Continuing it further, 06/01/1951 and 07/01/1951 has no value and so their value will be 20.87 i.e. value from immediate preceding date. And return for NN5 and NO5 will also be 0% but as it is a non working day, its value should not be shown. So, the solution should show 0% return if its a working day and not to show 0% if its a non working day.

 

I will appreciate any questions to be clarified. Also, any alternate solutions are also highly welcomed. The spreadsheet is attached herewith.

 

Thanks!

3 Replies
Highlighted
Best Response confirmed by Dharmendra_Bharwad (Contributor)
Solution

@Dharmendra_Bharwad 

Not to show days-off the formula for the rule

image.png

could be

=ISERROR(MATCH(DATE($AK4,MONTH(AM$2),DAY(AM$2)),$A:$A,0))
Highlighted

Thanks alot @Sergei Baklan. The formula and rule both works very accurately. 

Thanks again for repeated help!

Highlighted

@Dharmendra_Bharwad , you are welcome, glad to help