REALLY need your help with a function -- days360 network days format

[cen03]

So I am trying to calculate the number of days between two dates that are in dd/mm/yyyy form.
Now I know that there are: Days360 and NetworkDays functions HOWEVER they work on mm/dd/yyyy forms

I have over 3000 inputs that need to be done and I cannot change each and every date to a mm/dd/yyyy form so is there any way that either there is a formula that works on dd/mm/yyyy formats OR to change the dd/mm/yyyy automatically into mm/dd/yyyy when I copy paste it into the excel sheet so that the functions work??

Please any help is appreciated thanks so much in advance

[Bob Phillips]

Functions do not work on date formats, they work on the underlying dates. As long as your data is dates, not text, no matter how it is formatted, both of those functions will work upon it.

[cen03]

But when its in dd/mm/yyyy format it doesnt work ==> it gives me #Value!
but when i change it manually to mm/dd it works

[Bob Phillips]

That must be because they must be text, not real dates.

Post the workbook and le's take a look.

[cen03]

Yes its text! how to fix this? im copy pasting it from a page.

[cen03]

here's the workbook

[cen03]

bumping up.. anyone?

[Bob Phillips]

Try this

=IFERROR(NETWORKDAYS(C14,$A$1),NETWORKDAYS(SUBSTITUTE(C14,".","/"),$A$1))

[cen03]

where do i paste this?

[cen03]

stil doesnt give me values for 14 and 18..

if i edit c14 and c18 manually into a mm/dd format, it works... but i need smthing that changed them into mm/dd so i dont have to manually change each one

[BarryTSL]

Can you use this in a hidden column

Please Login or Register  to view this content.

and calculate days from here.

[Bob Phillips]

Works for me.

Actually this is better, handles the empty dates

=IFERROR(NETWORKDAYS(SUBSTITUTE(C5,".","/"),$A$1),"")