• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Correct date not showing

Your so was hoping we can do something with it and covert this col to valid format date
Did You skip my given solution for that?
+ as You're playing only with formulas -- this could solve with VBA.
Here one possible solution as I've tried to offer for You.
 

Attachments

  • Book test.xlsx
    131.2 KB · Views: 2
Your so was hoping we can do something with it and covert this col to valid format date
Did You skip my given solution for that?
+ as You're playing only with formulas -- this could solve with VBA.
Here one possible solution as I've tried to offer for You.
Thanks for the file again, I am getting confused and furstrated now, In your file attached i can see in unmessed Col j everything going smooth but when i replicate same thing in my file, attached , incol. Q it is not working, can you please let me know for last time, what mistake i am doing
 

Attachments

  • Book test_VV.xlsx
    120.9 KB · Views: 4
What are your regional settings on the computer? Everything suggested so far involves text manipulation that is based on the date format that your computer will recognise. It would appear at first glance that your computer does not use day/month/year order, which makes me think you are set to a US region?
 
What are your regional settings on the computer? Everything suggested so far involves text manipulation that is based on the date format that your computer will recognise. It would appear at first glance that your computer does not use day/month/year order, which makes me think you are set to a US region?
Yes it is US region only, so if this is the reason then can we do something with col.J to make it compatibile with my computer settings?? Note:- Cant change my setting in my system, as it is blocked from my company :-(
 

Tom22

Did You even try to do same as I've done?
Did You start Your test with cell which Custom Format is General?
... if not - then test with that setting (otherwise many things would happen).
All dates should be written same way in file.
( If someone mess by writing 27/03/2024 and 03/27/2024 then even Excel will start wondering ... is Month from 03/08/2024 ... March or August. )
Why did You used Custom Format with that column?
I tested ... everything works ... as it should work.
 

Attachments

  • Book test_VV.xlsx
    118.4 KB · Views: 2

Tom22

Did You even try to do same as I've done?
Did You start Your test with cell which Custom Format is General?
... if not - then test with that setting (otherwise many things would happen).
All dates should be written same way in file.
( If someone mess by writing 27/03/2024 and 03/27/2024 then even Excel will start wondering ... is Month from 03/08/2024 ... March or August. )
Why did You used Custom Format with that column?
I tested ... everything works ... as it should work.
Bro just tell me which format should i take from your file, I would prefer format in cell Q3, but when I tried to copy and paste the same in blank cells after row 11, it still shows error message, why???
 

Attachments

  • Book test_2.xlsx
    118.5 KB · Views: 3
Your ... Bro
Did You noticed that I tried to get answers to three questions?
# Did You try ...
# Did You format that cell with Custom Format as General?
I can write that copy & paste won't be a solution.
Or it could be if You would paste only needed.
... a value or in this case a formula.
If You'll paste as 99% will do ... by pasting 'everything' then the mess continues.
First, You gotta get valid value to cell
and
after that make it look as You would like it to look.
 
Your ... Bro
Did You noticed that I tried to get answers to three questions?
# Did You try ...
# Did You format that cell with Custom Format as General?
I can write that copy & paste won't be a solution.
Or it could be if You would paste only needed.
... a value or in this case a formula.
If You'll paste as 99% will do ... by pasting 'everything' then the mess continues.
First, You gotta get valid value to cell
and
after that make it look as You would like it to look.
I am not sure why we are making this so complicated. to answer your questions above "yes i tried everything and sending you updated file every time" and you see i am not getting desired output. in the last file you shared, you have made some changes, obviously, in the file and row 5 showing correct output col. R, so can i request you to just tell me what format you have used in col. Q and in cell V1, in order to get correct output in col.R, as you are getting till row 10
 
I've learnt that without proper answers it'll be pure guessing
- what has other done? ... even with which file.
# Have You read:
First, You gotta get valid value to cell
and

after that make it look as You would like it to look.

# Did You format that cell with Custom Format as General?


If You'll answer that 'yes' then
... why do You ask again ... can i request you to just tell me what format you have used in col ...
General

You asked cell's V1 Custom Format
Screenshot 2024-05-04 at 14.53.04.png
... but it no matter of format ... it matters what is in cell!

Please check this files X-column - do those look working?
Ps. I've noticed that sometimes these works as these works ... and gives unwanted results.
Then it would be more smoother to start from new file and paste there ONLY values or formulas.
... above means ... something else than normal copy & paste.
 

Attachments

  • Book test_VV.xlsx
    119.1 KB · Views: 4
I've learnt that without proper answers it'll be pure guessing
- what has other done? ... even with which file.
# Have You read:
First, You gotta get valid value to cell
and

after that make it look as You would like it to look.

# Did You format that cell with Custom Format as General?


If You'll answer that 'yes' then
... why do You ask again ... can i request you to just tell me what format you have used in col ...
General

You asked cell's V1 Custom Format
View attachment 87206
... but it no matter of format ... it matters what is in cell!

Please check this files X-column - do those look working?
Ps. I've noticed that sometimes these works as these works ... and gives unwanted results.
Then it would be more smoother to start from new file and paste there ONLY values or formulas.
... above means ... something else than normal copy & paste.
Ok let me try it again, in cell Q9, we have format as General,I copied it and pasted i cell Q12 and it gives erroe message , now can you plz explain why this formula is working in Q9 and not in Q12, when every thing is same.
 
You seems to skip my question about X-column?
... without Your answers, there are challenges!

Your why ... ?
#1 As many times ...
I can write that copy & paste won't be a solution.
Or it could be if You would paste only needed.
... a value or in this case a formula.
# Do those cells look like below BEFORE any format or paste?
Screenshot 2024-05-05 at 09.29.42.png

# Do those cells look like below AFTER format and paste?
Screenshot 2024-05-05 at 09.31.29.png

# Can You format WHOLE Q-column as General?
... and after that copy and paste Q9 to Q12?

# Can You answer about X-column?

# Can You answer - how do You download data to that file?
 
You seems to skip my question about X-column?
... without Your answers, there are challenges!

Your why ... ?
#1 As many times ...
I can write that copy & paste won't be a solution.
Or it could be if You would paste only needed.
... a value or in this case a formula.
# Do those cells look like below BEFORE any format or paste?
View attachment 87208

# Do those cells look like below AFTER format and paste?
View attachment 87209

# Can You format WHOLE Q-column as General?
... and after that copy and paste Q9 to Q12?

# Can You answer about X-column?

# Can You answer - how do You download data to that file?
1. I made whole Col Q as General and then copy paster from Q9 to Q12, but getting same error message
2. About col x , yes that look will work or any look will work until we have correct output in col. R,

By the way, we should refer to V1 cell not U1 cell in the formula in col.R
 
# ... Before ...?
# ... After ... ?
# Seems You could format.
# What same error message?
# Yes, with V1 it could work better.
# You cannot answer - how do You download data to that file?
# How about this file?
Is there something which works?
Is there something which gives #VALUE! or something unwanted?
... and no matter of how do it show any values!
 

Attachments

  • Book test_VVV.xlsx
    125 KB · Views: 1
Here one more possible way to do this ...
Read Test-sheet's instructions before You'll do something.
 

Attachments

  • Book test_VVV.xlsb
    124.1 KB · Views: 3

Tom22

Do something working with #41 sample?
Do something working with #42 sample?
... those samples won't help You, if You still skip to answer - how do You download data to that file?
 
Back
Top