• 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.

need help on formatting

J

jayalaxmi

Active Member
Dear all,

I am trying to format date in column D. But it is not getting formatted all with the same formats. Because of that my formula in column L & col M is not working. kindly help.
Thanks in advance
 

Attachments

  • help.xlsx
    306.4 KB · Views: 6
Hello narayan,


I am not getting why I am getting #value error in Column L..I m Tired of identifying this error. Pls help

Regards
 
Hello narayan,

I am also attaching the reference file to understand you better in which I had already worked earlier
 

Attachments

  • Open_Jobs_12052016.xlsx
    18.7 KB · Views: 7
Hi Jaya ,

Sorry , but I don't think I can help.

When I open the same workbook at my end , I don't see any error values.

If you are finding an error value , see if you can check out the calculation so that you can find out from which step of calculation the error value is being generated.

Place the cursor in the cell , say L4 , and click on the Evaluate Formula button in the Ribbon. Thereafter , keep clicking the Evaluate button to progress through the calculation steps.

Try opening the workbook in some other computer.

Narayan
 
Chihiro said:
@jayalaxmi

Ok, issue here is that J column is text function. Change that to...
in J2:
=INT(D2)

You should see #Value error disappear after that.
Click to expand...
Hi Chihiro ,

No issues here ; the data in column J is text , but they are all valid dates in my system ; probably Jayalaxmi's system date format is different , which is why Excel might generate an error , but then this error should be in column L , and not only in column M.

If the data in column L has no error values , then the data in column M alone cannot have any error values.

Jaya : can you indicate whether your system date format is mm/dd/yyyy ? If so , that is the reason , and you need to change the formula in column J to the following :

=DATEVALUE(MID(D2,4,2) & "/" & LEFT(D2,2) & "/" & MID(D2,7,4))

Narayan
 
Last edited:
@NARAYANK991

Right, my regional default is mm/dd/yyyy that's why I get error in K & L from her sheet (corresponds to your sheet's L & M). I assume Marc and you have default date in dd/mm/yyyy format?

@jayalaxmi
Some of dates works fine as days between 1~12 gets interpreted as months. Though it will give unexpected results.
Ex. dd/mm/yyyy of 05/09/2016 gets interpreted as May 9th 2016 etc.

As general practice, I only use =Text(,"date format") for date only when I know people that use the sheet share same regional setting (though I use =Text(,"ddd") or =Text(,"mmm") often).
 
thank you guys..for all your comments and help..

Narayan thank again for the same
=DATEVALUE(MID(D2,4,2) & "/" & LEFT(D2,2) & "/" & MID(D2,7,4))

Excel is playing wonders I think..:eek::rolleyes:
actually you know what after trying this formula i had got results right..Basically the problem is in my sheet column D some date are in this format
10/6/2016
and some are in
13/06/2016 01:00 PM
this format..I tried changing format for the column in mm/dd/yyyy etc.and many other possibilites. But the output didnt reflected for the whole column. Even I tried applying int() in J column. But it came out with #value error. So I am stucked in between these two..So that is the case I am getting #value error may be. Changing the date manually for the whole in one format is bit hectic for me.. So kindly guide.


Regards
 
Hi Jaya ,

The issue is that the format is something that will come into play only for valid dates ; for dates which Excel cannot recognize as valid dates , Excel will treat them as text , and for text strings , date formats will do nothing.

So , the first step is to ensure that all your dates are valid dates.

It is of no use if some dates are recognized as valid dates.

If your system date format ( which is solely to do with your operating system , and nothing to do with Excel ) is mm/dd/yyyy , then a date such as :

1/3/2016

will be interpreted as January 3 , 2016.

If your system date format is dd/mm/yyyy , then a date such as :

1/3/2016

will be interpreted as March 1 , 2016.

When you are processing thousands of dates spanning several months , such mis-interpretation of dates can be missed.

Hence , the starting point is to ensure that the dates in your worksheet are interpreted correctly. For this , you need to ensure that your data is in the same format as your system date format.

Thus , if your dates are :

24/06/2016 , with or without a time component

then , if your system date format is dd/mm/yyyy , there is no problem , since Excel will interpret all your dates correctly.

However , if your system date format is mm/dd/yyyy , there will be two problems :

1. Dates where the month component is less than or equal to 12 will be treated as valid dates , but interpreted wrongly. 11/9/2016 will be interpreted as November 9 , whereas it should be interpreted as September 11.

2. Dates where the month component is greater than 12 will be treated as invalid dates.

Narayan
 
NARAYANK991 said:
Hi Jaya ,

The issue is that the format is something that will come into play only for valid dates ; for dates which Excel cannot recognize as valid dates , Excel will treat them as text , and for text strings , date formats will do nothing.

So , the first step is to ensure that all your dates are valid dates.

It is of no use if some dates are recognized as valid dates.

If your system date format ( which is solely to do with your operating system , and nothing to do with Excel ) is mm/dd/yyyy , then a date such as :

1/3/2016

will be interpreted as January 3 , 2016.

If your system date format is dd/mm/yyyy , then a date such as :

1/3/2016

will be interpreted as March 1 , 2016.

When you are processing thousands of dates spanning several months , such mis-interpretation of dates can be missed.

Hence , the starting point is to ensure that the dates in your worksheet are interpreted correctly. For this , you need to ensure that your data is in the same format as your system date format.

Thus , if your dates are :

24/06/2016 , with or without a time component

then , if your system date format is dd/mm/yyyy , there is no problem , since Excel will interpret all your dates correctly.

However , if your system date format is mm/dd/yyyy , there will be two problems :

1. Dates where the month component is less than or equal to 12 will be treated as valid dates , but interpreted wrongly. 11/9/2016 will be interpreted as November 9 , whereas it should be interpreted as September 11.

2. Dates where the month component is greater than 12 will be treated as invalid dates.

Narayan
Click to expand...
Thank you narayan..U caught it right... :).Big thanks :)

regards
jaya
 
You must log in or register to reply here.
Back
Top