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.