Time stamp using with "if,countif" function...

[madhanji]

Dear Seniors.Excel Masters
i have attach the image for your reference,
A column should be "today date stamp" when column B has "NRML" string,
so i used IF condition

IF(B20="","",IF(B20="NRML",now(),"HI"))

it's working fine..
but while drag-down the formula time stamps shows A column but i dont need it..
first date is only ok for entire day...
means as per attached picture
so i tried countif function but not succeed as per my knowledge..
please help...

Thanks in advance..

 

[madhanji]

ok sir, i will check tomorrow and revert you sir..

Thanks IN ADVANCE.

 

[madhanji]

Change the formula to

=IF(B2="NRML",IF(A2<>"",A2,TODAY()),"")

Then use conditional formatting to change the font colour to white in the rows where the date is not needed.

Select A2:A100, then go to conditional formatting.
Create a new rule, using a formula. The formula to use will be

=A2=A1

Set the font colour and click OK / Apply as applicable to finish setting the rule.

Dear Jason sir, the above code is working as you described with conditional formatting but in this formula Today function shows today date ,means of previous date data also change to today date..
so that A Column fully shows today date ...but my requirement is which day i enter NRML in column B then corresponding column A should be same day date..


Hope you will catch my point sir..
expect solution ...

 

[jasonb75]

If you have done it exactly as instructed then the previous dates will not change.

 

[madhanji]

Dear Jason sir, i am not able to succeed , i believe somewhere missing in my side..

anyway Thanks for your CONTINUES support..
if you find any other method to solve this issue please let me know..

Thanks and Happy life...

 

[jasonb75]

Have you changed the formula? If yes, what have you changed it to?

Is the first formula in A2? If not, where is it? Did you edit the formula to reflect this?

Have you deleted the "NRML" entries from the rows where the date is changing? Re-entering the text will cause the date to change.

 

[madhanji]

Dear Jason, again 2 days fully i tested/checked but not succeed,,issue is TODAY function works today date means yesterday date also changed today date..so A1 column fuly shows TODAY date due to formula contain TODAY..(i guess maybe this make issue)

Thanks dear...

 

[jasonb75]

i guess maybe this make issue

No, this is not the issue. It works perfectly for me. Unless you (accurately) answer the questions that I asked, I will be unable to assist further. There is something that you have done incorrectly but you have not provided the information that I asked for in order to try and find the problem. I can not see your screen or read your mind.

 

[madhanji]

Dear Jason sir, i attached a 9mb video file for your reference,of "what i do"for today 19.02.2021

click me

tomorrow i will update another video for next day date ..

Happylife.

 

[jasonb75]

The only thing I can see that you have done differently is to start the conditional formatting range at A1 instead of A2, but that doesn't appear to make any difference. Even if it did, it would not change the previous dates, only hide the wrong one behind the white font.

For testing, use this formula in the worksheet and format the cells as hh:mm:ss

=IF(B2="NRML",IF(A2<>"",A2,FLOOR(NOW(),TIME(0,0,10))),"")

and change the conditional format font to red instead of white.

The formula works on 10 second intervals, allowing you to do a week of testing in just over a minute. The principle of both methods is the same, so what works or doesn't work will be identifiable from it.