**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.*