USING IF STATEMENT IN EXCEL WITH MODIFIED DATE FORMAT

I have a cell, 1A, which is formatted as follows

[>99999]##-##-##;"0"#-##-##

The format allows our users to key in the date as 42715 which displays as 04-27-15.

In 1B, I want to test for the contents of 1A, if any value exists, I want to display the value of 1A in 1B, if not I want to use today's date.
LVL 1
Bill GoldenExecutive Managing MemberAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
You can simply use this...

=if(a1="",today(),a1)

Saurabh...
0
Bill GoldenExecutive Managing MemberAuthor Commented:
Unfortunately that doesn't work because...
1A has a custom format [>99999]##-##-##;"0"#-##-## and not a date format
If I use use the same custom format is 1B, 41515 is shown as 04-15-15.
However, if 1A has not value, today's date displays as 04-21-21.
If I use a date format in 1B, today's date does return as 04-27-15,
but the value of 1A (04-15-15) then returns as 08/28/13.
0
Rgonzo1971Commented:
Hi,

pls try in B1 with date format

=IF(A1="",TODAY(),TEXT(A1,"[>99999]##-##-##;""0""#-##-##"))

Regards
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

ProfessorJimJamMicrosoft Excel ExpertCommented:
what exactly do you want to see in B1? if A1 is empty?  or if A1 has 41515 then what exactly to you want B1 to show?
0
ProfessorJimJamMicrosoft Excel ExpertCommented:
I think Rgonzo1971 nailed it.
0
scsymeCommented:
Two alternatives are:

1. Keep everything in column B as per column A, not really stored as a date, but viewed as a date.
=IF(A7<>"",A7,MONTH(TODAY())*10000+DAY(TODAY())*100+YEAR(TODAY())-2000)

Open in new window


OR

2. Make everything in column B a real date.
=IF(A2="",TODAY(),DATE(2000+RIGHT(A2,2),LEFT(A2,IF(LEN(A2)=5,1,2)),MID(A2,LEN(A2)+1-4,2)))

Open in new window

In the second case set the format of column B to mm-dd-yy
0
Bill GoldenExecutive Managing MemberAuthor Commented:
Rgonzo1971, you did indeed nail it.

Thanks to everyone else for the input.

Bill
0
Rob HensonFinance AnalystCommented:
Bill - bigger question - why do you want your users to be able to input 42715 as the date. Will this not get quite confusing when looking at real numbers and real dates?

With dates excel will normally recognise a partial date entry, ie in the UK entering 27/4 will recognise as date and will populate as 27/04/15 or if preformatted as another date format, ie mm-dd-yy in your case, it will adopt that format. I assume in US entering 4/27 will do the same. This may require a slight change to Settings; the Lotus Transition settings should have the Formula Entry option unchecked, with this checked entering 27/4 will enter it as a sum and be the equivalent of entering =27/4.

Thanks
Rob H
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.