Date Formatting

paul29berks

Active Member
Joined
Mar 15, 2004
Messages
293
I wondered if someone could help me.

I have devised a statement that formats dates:

20061020 to 20/10/2006 or if 00000000 then format as null.

IIf([Leaving_Date]="00000000",Null,DateValue(Mid([Leaving_Date],7,2) & "/" & Mid([Leaving_Date],5,2) & "/" & Mid([Leaving_Date],1,4)))

However if I my original format is null how can I include into my statement?

Thanks

Paul
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
if I understand the question correctly then either of these options should work for you...


IIf(nz([Leaving_Date],"00000000") ="00000000",Null...

or this

IIf(isnull([Leaving_Date]) OR [Leaving_Date] ="00000000",Null...

hth,
Giacomo
 

Watch MrExcel Video

Forum statistics

Threads
1,113,845
Messages
5,544,629
Members
410,626
Latest member
rkmadasu
Top