All solutions IF Expert Solution – Excel IF Problems

Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

i have this formula currently which works but not if I want it for every day - any ideas ?? (NETWORKDAYS.INTL(L1362,M1362,11,BH!A:A)-1)*("17:30"-"8:30")+IF(NETWORKDAYS.INTL(M1362,M1362,11,BH!A:A),MEDIAN(MOD(M1362,1),"8:30","17:30"),"17:30")-MEDIAN(NETWORKDAYS.INTL(L1362,L1362,11,BH!A:A)*MOD(L1362,1),"8:30","17:30")
Solved by A. D. in 28 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
User 09/05/2018 - 02:52
hi
Excelchat Expert 09/05/2018 - 02:52
Welcome, Thanks for choosing Got It Pro-Excel.
Excelchat Expert 09/05/2018 - 02:52
Do you have sample data that we can work on?
User 09/05/2018 - 02:53
(NETWORKDAYS.INTL(L1362,M1362,11,BH!A:A)-1)*("17:30"-"8:30")+IF(NETWORKDAYS.INTL(M1362,M1362,11,BH!A:A),MEDIAN(MOD(M1362,1),"8:30","17:30"),"17:30")-MEDIAN(NETWORKDAYS.INTL(L1362,L1362,11,BH!A:A)*MOD(L1362,1),"8:30","17:30")
Excelchat Expert 09/05/2018 - 02:53
Just a moment please as I go through it...
User 09/05/2018 - 02:53
I want to count the hh:mm from two columns but oinly for 830 to 530
User 09/05/2018 - 02:54
but make it 7 days a week
User 09/05/2018 - 02:54
there must be a shorted easier query to run
Excelchat Expert 09/05/2018 - 02:55
Okay.
Excelchat Expert 09/05/2018 - 02:56
Anything else...
User 09/05/2018 - 02:57
no I just need it to bring back the hours mins between 2 fields - but between a criteria of 8am to 530pm
User 09/05/2018 - 02:59
Any ideas ?
Excelchat Expert 09/05/2018 - 03:02
Thank you for your patience.
Excelchat Expert 09/05/2018 - 03:02
First of all, dealing with date and time is very sensitive.
Excelchat Expert 09/05/2018 - 03:03
For them to work properly, we must ensure we have the correct data types.
User 09/05/2018 - 03:04
yep that's fine - the format is fine - the formula doesn't bring back the data on weekends
Excelchat Expert 09/05/2018 - 03:05
So you need all the weekends to be inclusive?
Excelchat Expert 09/05/2018 - 03:06
Are you there?
User 09/05/2018 - 03:09
yes
User 09/05/2018 - 03:10
all weeknds just between certain hours
Excelchat Expert 09/05/2018 - 03:10
Kindly allow the time extension so that we can be able to chat.
Excelchat Expert 09/05/2018 - 03:11
In that case we will not use the NETWORKDAYS.INTL function
User 09/05/2018 - 03:11
ok
Excelchat Expert 09/05/2018 - 03:12
NETWORKDAYS.INTL function always excludes weekends which you specify in its parameters.
Excelchat Expert 09/05/2018 - 03:12
Instead use:
Excelchat Expert 09/05/2018 - 03:12
DAYS360 function
Excelchat Expert 09/05/2018 - 03:14
OR
Excelchat Expert 09/05/2018 - 03:15
DATEDIF function
Excelchat Expert 09/05/2018 - 03:15
DATEIF function Calculates the number of days, months, or years between two dates.
Excelchat Expert 09/05/2018 - 03:15
Its syntax is:
Excelchat Expert 09/05/2018 - 03:15
DATEDIF(start_date,end_date,unit)
Excelchat Expert 09/05/2018 - 03:16
Where:
Excelchat Expert 09/05/2018 - 03:16
Is a date that represents the first, or starting, date of the period. Dates may be entered as text strings within quotation marks (for example, "2001/1/30"), as serial numbers (for example, 36921, which represents January 30, 2001, if you're using the 1900 date system), or as the results of other formulas or functions (for example, DATEVALUE("2001/1/30")).
Excelchat Expert 09/05/2018 - 03:17
End_date
Excelchat Expert 09/05/2018 - 03:17
A date that represents the last, or ending, date of the period.
Excelchat Expert 09/05/2018 - 03:17
Unit
Excelchat Expert 09/05/2018 - 03:17
The type of information that you want returned:
Excelchat Expert 09/05/2018 - 03:17
"Y" for number of years
Excelchat Expert 09/05/2018 - 03:17
"D" for number of days
Excelchat Expert 09/05/2018 - 03:17
"M" for number of months
Excelchat Expert 09/05/2018 - 03:18
Otherwise if your system is based on 30-day month, 360-year system, use the DAYS360 function.
Excelchat Expert 09/05/2018 - 03:19
In summary, replace the NETWORKDAYS.INTL function.
Excelchat Expert 09/05/2018 - 03:19
Thank you for your patience.
Excelchat Expert 09/05/2018 - 03:20
If you get issues solving this, kindly let us know.
Excelchat Expert 09/05/2018 - 03:20
Any time you have a problem with Excel do not hesitate to come back for help. Enjoy the rest of your day!

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc