NETWORKDAY(S) not working for me
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!
Answers
-
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.
-
I am using =MID([Due]@row, 9, 2) + "/" + MID([Due]@row, 6, 2) + "/" + MID([Due]@row, 1, 4)
-
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(..............)))
-
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.
-
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)))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!