NETWORKDAY(S) not working for me

Martin Suchy

Hi - any idea why I am not able to get a formula NETWORKDAYS working? I've have below columns where due date and start date converted having formulas to get those dates. The formula is just "MID" combination and both columns are set to "date" format.

I tried using different ways of NETWORKDAY(S) but I am only getting #invaliddata as result.

Thanks for any hint here!

Paul Newcome

What are the formulas you are using to convert the dates? It could be possible that the data is being stored as a text value instead of a date value.

Martin Suchy

Hi @Paul Newcome

I am using =MID([Due]@row, 9, 2) + "/" + MID([Due]@row, 6, 2) + "/" + MID([Due]@row, 1, 4)

Paul Newcome

Ok. So that formula is generating a text string instead of an actual date. You are going to want to wrap each MID function in a VALUE function and then drop them into the appropriate portion of a DATE function.

=VALUE(MID(...........))

=DATE(yyyy, mm, dd)

=DATE(VALUE(MID(..............)), VALUE(MID(..............)), VALUE(MID(..............)))

Martin Suchy

Thanks but actually it does not work.. may be I am doing something wrong.

Here the dates which I am getting form other sheets using index/match formulas. It might be the reason why =Date(value(mid.....) does not work here.

Paul Newcome

If this is your current formula:

=MID([Due]@row, 9, 2) + "/" + MID([Due]@row, 6, 2) + "/" + MID([Due]@row, 1, 4)

Then this should work:

=DATE(VALUE(MID([Due]@row, 1, 4)), VALUE(MID([Due]@row, 6, 2)), VALUE(MID([Due]@row, 9, 2)))