Date Format

[Craig]

I have some external data which is imported into excel and might contain
full stops in dates and Excel doesn't seem recognise this very well, is
there a way to force Excel to?

01/01/2005 05/01/2005 =NETWORKDAYS(B4,C4) (show as "3", great)
01.01.2005 05.01.2005 =NETWORKDAYS(B5,C5) (show as "#VALUE!")

I'd be grateful for any help.
Craig

 

[JulieD]

Hi Craig

try
=NETWORKDAYS(SUBSTITUTE(B5,".","/"),SUBSTITUTE(C5,".","/"))

 

[Dave Peterson]

I'd change the dots to slashes so that excel will see them as dates.

Select your range of cells (don't select too much!)
edit|replace
what: . (dot)
with: / (slash)
replace all

Then your formula will be ok.

 

[Craig]

This looks great,
many thanks

Craig

 

[JulieD]

you're welcome