Correct date not showing

[Tom22]

Hi Guys,

I am trying to get date in col. Q from col.M. Logic is if my date in col. J is less than date mentioned on cell Y1, then it should return the value on col. J else return "0".
It should be very simple formula but if you see in col. J date format is different, so the question is:-" How to simplify date to show only "DD-MM-YYYY" in col. J and then applied the if formula in Col. Q'
I tried several methods but not able to get the desired results. Thanks

 

[rollis13]

I'm not sure what cell format of column J and cell V1 have (should be date, but it doesn't), so use this formula in column Q and pull down.
I'm multiplying by one column J to convert it to date and using integer to delete the time and keep only the date:

=IF(INT(J2*1)<$V$1*1,J2,0)

 

[Tom22]

I'm not sure what cell format of column J and cell V1 have (should be date, but it doesn't), so use this formula in column Q and pull down.
I'm multiplying by one column J to convert it to date and using integer to delete the time and keep only the date:

=IF(INT(J2*1)<$V$1*1,J2,0)

Thanks rollis13 but it is not working, it showing #value error.
And to answer your question above about format, that is something i am confused, I get this data as it is from some other tool.
Attaching file after applying your formula

 

[rollis13]

So why is column Q now formatted "Custom" dd-mm-yyyy ?
I suggest you should format the column Q as "General" before pasting my formula.

 

[Tom22]

So why is column Q now formatted "Custom" dd-mm-yyyy ?
I suggest you should format the column Q as "General" before pasting my formula.

Not sure if this makes any difference,but neverthless i tried and no change in the Q col.
Attaching it with "Genaral"

 

[vletm]

Please, use valid date in cell V1.
eg

 

[rollis13]

So let's eliminate the issue like this:
- write 1 in a helper-cell (a free one formatted General)
- Copy it
- select the entire column Q
- right-click on column Q and choose Paste Special
- select Multiply and then OK
- delete the helper-cell

 

[Tom22]

Please, use valid date in cell V1.
View attachment 87163 eg View attachment 87164

Thanks, it is working now...

 

[rollis13]

So now you can trim the formula to:
=IF(INT(J2*1)<$V$1,M2,0)

 

[Tom22]

Sorry guys it is not working as per requirements. @rollis it is still showing #value error and @vletm it is not showing correct results, like row 33 in attached workbook. it is more than 24 march but still showing 1 as answer, where as it should be 0.

 

[vletm]

Tom22​

...hmm?
Now, You're using cell U1 for something.
BUT - why do You still use there something else than date/number?

Could You please write there 24/3/2024 or 45375?
... those both are same.
If You are writing/talking about dates - then please use dates.
Your Sunday 24 March 2024 is text which looks like date.

 

[Tom22]

Tom22​

...hmm?
Now, You're using cell U1 for something.
BUT - why do You still use there something else than date/number?
View attachment 87168
Could You please write there 24/3/2024 or 45375?
... those both are same.
If You are writing/talking about dates - then please use dates.
Your Sunday 24 March 2024 is text which looks like date.

It doesnt make any difference even if i use 24/03/2024..Attaching file with the udpate

 

[rollis13]

To get your sheet to work all you have to do is to go to cell U1, press key F2 and then confirm with key Enter.

 

[vletm]

Tom22​

How did You ... use ... it?
#1 I opened Your above file
#2 I select cell U1
#3 I select from formula bar right side of 4

#4 I press <ENT> and ... hmm?
... same effect comes if press <TAB> instead of <ENT>

... hmm?
Do You copy and paste something somewhere?

 

[Tom22]

Tom22​

How did You ... use ... it?
#1 I opened Your above file
#2 I select cell U1
#3 I select from formula bar right side of 4
View attachment 87171
#4 I press <ENT> and ... hmm?
... same effect comes if press <TAB> instead of <ENT>
View attachment 87170
... hmm?
Do You copy and paste something somewhere?

How you are getting true and I am getting false for the same value, thats the confusion here, can you please let me know how to correct the format so that forumla in col. Q give correct output, made corrections in Col. Q in the file to get value on the basis of Col.J....please advice

 

[vletm]

Tom22​

Did You follow those four steps I gave?
You skipped to answer: Do You copy and paste something somewhere?
Have You tried to write there 45375 and press <ENT>?
>>> Without You answers, other could only wonder - what are You doing there? <<<
ps. I tested both again to write that date as well as that number - both works well.

About the format ... forget now any format!
With format ... You could control - what do You see?
If You really would like to use format now - then format that cell as General.

 

[Tom22]

Tom22​

Did You follow those four steps I gave?
You skipped to answer: Do You copy and paste something somewhere?
Have You tried to write there 45375 and press <ENT>?
>>> Without You answers, other could only wonder - what are You doing there? <<<
ps. I tested both again to write that date as well as that number - both works well.

About the format ... forget now any format!
With format ... You could control - what do You see?
If You really would like to use format now - then format that cell as General.

To answer your question:- I am not copy and pasting anything anywhere.

Even after converting 24/3/2024 to 45375, it showing zero output everywhere.

 

[vletm]

# cell P2
D2 seems to be empty ... D2*1 is 0 - or what else it should be?
... why there is "0" ... text again?
# cell Q2
J2 seems to be text ... 'text' < U1 ... false - then it gives "0" ... text zero again ... hmm?

# D28 ... as well all those D-column values which has something visible ... mess.
There is text which looks like 31 ... If You would like to compare it with other number then ... mess.

Did I miss something?

 

[Tom22]

# cell P2
D2 seems to be empty ... D2*1 is 0 - or what else it should be?
... why there is "0" ... text again?
# cell Q2
J2 seems to be text ... 'text' < U1 ... false - then it gives "0" ... text zero again ... hmm?

# D28 ... as well all those D-column values which has something visible ... mess.
There is text which looks like 31 ... If You would like to compare it with other number then ... mess.

Did I miss something?

My col. P is working fine, what i am trying to do here is:-
converting text to number in col. P from Col. D, but as we have some blanks (making them zero) and "PSA" (Taking it PSA only)
Real issue is col. J and Col.Q, the data i am getting is laid out like col. J only (date and time) and idont want to consider all the numbers in col. M if they are more than date mentioned on cell U1.

Now date on col. J is dont know which format and what format we should take in cell U1, so that my output in Col. Q gives right answer.

 

[vletm]

#1 If You use numbers then use numbers!
#2 cell P2 =IF(D2="PSA","PSA",IFERROR(D2*1,0))
#3 If You really have to use those J2-text then cell Q2 =IF(SUBSTITUTE(J2,"-","/")*1<$U$1,M2,0)

 

[Tom22]

#1 If You use numbers then use numbers!
#2 cell P2 =IF(D2="PSA","PSA",IFERROR(D2*1,0))
#3 If You really have to use those J2-text then cell Q2 =IF(SUBSTITUTE(J2,"-","/")*1<$U$1,M2,0)

Applied your last formula =IF(SUBSTITUTE(J2,"-","/")*1<$U$1,M2,0) but it showing value as error,it seems we are going round and round on the same thing and i was wondering it would be a very small thing to do in this group of experts.

 

[vletm]

Tom22​

Okay ... Your data in J-column seems to have many many typical mistakes which someone would have.
Basic reason for this is that .. which I've written few times
... Your J-column values should be date and time.
Now, You've to fix someones ... duty ... to clean those every time.
Of course, if You've time to do it ... then maybe okay for You.
I tested with my previous solution one row data ... it works.
BUT
later, some datas has more mess which can solve by taking some extra character away.
Check that marked range.
>> Better solution: Get valid data for J-column. <<

About: i was wondering it would be a very small thing to do in this group of experts.
There are none very small things.
There could be possible things to solve if someone could give needed details at once.
It would help many times if someone would give answers to questions too.

 

[Tom22]

Tom22​

Okay ... Your data in J-column seems to have many many typical mistakes which someone would have.
Basic reason for this is that .. which I've written few times
... Your J-column values should be date and time.
Now, You've to fix someones ... duty ... to clean those every time.
Of course, if You've time to do it ... then maybe okay for You.
I tested with my previous solution one row data ... it works.
BUT
later, some datas has more mess which can solve by taking some extra character away.
Check that marked range.
>> Better solution: Get valid data for J-column. <<

About: i was wondering it would be a very small thing to do in this group of experts.
There are none very small things.
There could be possible things to solve if someone could give needed details at once.
It would help many times if someone would give answers to questions too.

Can't get valid date for col.J, it just get downloaded this only, is there any way we use some formula to convert it in valid date format???

Another thing,it doesnt look good to type 45375 instead of date secondly, why we have value error message after row 16 and why for above rows your formula is working

 

[vletm]

... can't get ...
If someone has done it that way then someone (same person) could get it correct way too.
Or could there be some challenges while 'download' those values? ... Could that do otherway?
... is there any way we use some formula to convert it in valid date format??? ...
Did You check that the latest formula?
Of course, if You can first solve that J-column text to eg S-column and
after that copy those values and paste ONLY values to J-column ...
... it doesnt look good to type 45375 instead of date secondly, ...
Isn't there some challenges to write 24/3/2024 in that cell?
... it matters, what is in that cell.
Of course, now You could use Custom Format that You could see something else eg below...
... Sunday 24-March-2024

... but if You'll write same (text Sunday 24-March-2024 ) in cell, then You'll be in same loop again.
... why we have value error message after row 16 and why for above rows your formula is working ...
As written Your data in J-column seems to have many many typical mistakes which someone would have.
Reason: later, some datas has more mess which can solve by taking some extra character away.
Of course, there could be more mess ... and then You could use CLEAN-function to ... clean some other typical messes away.
>> Better solution: Get valid data for J-column. <<

 

[Tom22]

... can't get ...
If someone has done it that way then someone (same person) could get it correct way too.
Or could there be some challenges while 'download' those values? ... Could that do otherway?
... is there any way we use some formula to convert it in valid date format??? ...
Did You check that the latest formula?
Of course, if You can first solve that J-column text to eg S-column and
after that copy those values and paste ONLY values to J-column ...
... it doesnt look good to type 45375 instead of date secondly, ...
Isn't there some challenges to write 24/3/2024 in that cell?
... it matters, what is in that cell.
Of course, now You could use Custom Format that You could see something else eg below...
... Sunday 24-March-2024
View attachment 87191
... but if You'll write same (text Sunday 24-March-2024 ) in cell, then You'll be in same loop again.
... why we have value error message after row 16 and why for above rows your formula is working ...
As written Your data in J-column seems to have many many typical mistakes which someone would have.
Reason: later, some datas has more mess which can solve by taking some extra character away.
Of course, there could be more mess ... and then You could use CLEAN-function to ... clean some other typical messes away.
>> Better solution: Get valid data for J-column. <<

Ok so conclusion is to get valid date in Col. J, which is not possible because as we have no control on this, so was hoping we can do something with it and covert this col to valid format date, so that it will look at cell U1 and give inputs in Col. Q, by the way thanks and appreciate all the time & pain you took for this. Cheers