Custom Date Formats 00/01/900
Good evening Fellow Forum Members...
I have a column of dates that are a result of a vlookup from a different sheet.
The results are as something like the below
00/01/1900
00/01/1900
00/01/1900
21/01/2014
21/02/2014
23/01/2014
00/01/1900
00/01/1900
Is there a way to use custom formatting to change the 00/01/1900 to either a blank or zero and still keep my other valid dates and if so how?
Look forward to your responses.
Try to apply condition as below structure:
=IF(VLOOKUP(...)=0,"",TEXT(VLOOKUP(...),"dd/mm/yyyy"))
Quang PT
HI There,
Similarly an if statement like the following did work
=IF('Original Data'!A1=0," ",'Original Data'!A1) If i used it in the "Linked from Original" sheet
I was hoping to avoid If statements. And use the Cell format box.
Any further ideas on how this could be done. I tried the custom formats with little success.
I have attached an example and the vlookup sheet highlights my challenge
This shoudl work
dd-mm-yy;;0;
If you arehappy with the results, please add to the contributor's
reputation by clicking the reputation icon (star icon).
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
To undo, select Thread Tools-> Mark thread as Unsolved.
http://www.excelaris.co.uk
Re: Custom Date Formats 00/01/900
Arrrrghhh ,I Hate it when it is so simple.:-)
Thank you that worked perfectly.
Also helped me understand some basic skills on the custom cell formats.
You are welcome.
Please add rep if you happy with the solution
The basic
There for "categories"
1;2;3;4
1- for positive values
2- for negative
3-for 0 values
and 4 for Text.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks