Expert Solution - Excel IF Problems

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

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

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

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")).