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?
 
Rgonzo1971Commented:
Hi,

pls try in B1 with date format

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

Regards
0
 
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
ProfessorJimJamCommented:
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
 
ProfessorJimJamCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.