Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-18-2018, 12:16 PM   #1
Tophan
Newly Registered User
 
Join Date: Mar 2011
Posts: 233
Thanks: 33
Thanked 0 Times in 0 Posts
Tophan is on a distinguished road
Need Networkdays.intl formula to return negative value

I am setting up a vacation roster and was using the networkdays.intl formula to calculate the number of days used per employee per month, excluding Saturdays, Sundays and bank holidays.


The formula was working until I got to one employee who went over the number of due days for the year, using one day from 2019. Instead of returning a -1 result (which is what I want to see) I'm getting a syntax # error.

Any suggestions?

For your further reference, this is how I have the roster set up

Column A - Employee name
Column B - Days brought forward from previous year
Column C - Days due for current year
Column D - Total days due

So my formula is D1-NETWORKDAYS.INTL(E1,F1,1,Holidays). The result should be the number of days remaining / carried forward to the next month or next year

Thanks in advance for your help


Last edited by Tophan; 10-18-2018 at 12:23 PM.
Tophan is offline   Reply With Quote
Old 10-19-2018, 08:19 AM   #2
Tophan
Newly Registered User
 
Join Date: Mar 2011
Posts: 233
Thanks: 33
Thanked 0 Times in 0 Posts
Tophan is on a distinguished road
Re: Need Networkdays.intl formula to return negative value

Figured it out...so simple an error it is embarrassing

As I was working with dates, I had the number format set to custom so when I entered this formula it was trying to convert it to a customized date format. I changed it to number with 0 decimal places and it worked.
Tophan is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
From formular to function Nsane Modules & VBA 2 07-20-2015 10:30 PM
Query calculated field, return zero if negative result (AC2007) AOB Queries 2 10-13-2014 01:17 AM
formular in query steve111 General 6 06-08-2014 01:31 AM
Query Formular help!!! WellowBS Queries 0 11-15-2013 03:31 AM
networkdays Grimmers Queries 1 09-09-2002 11:04 PM




All times are GMT -8. The time now is 10:29 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World