Hello,
I'm having trouble with counting different two cells in business days.
For example:
A1(10/1/2017 8:23AM) B1(10/1/2017 1:52PM)
If I do =NETWORKDAYS(A1,B1), it will return as "1" but what I really want is the decimal point as technically it was completed less than 1 business day.
Any help would be much appreciated.
Thank you,
What are the normal working hours on each day?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
8 hours. Is working hours the only way to accomplish this?
Is this a valid example, 1st October 2017 was a Sunday (or is it 10th January?) What are your working days? Will start and end times always be within working hours?Originally Posted by thomashur87
Audere est facere
I should have asked... What are the normal hours... 9 to 5 or what?
Well you need a base unit to present your result in. In other words if we went by your title "Days between two cells in business days" then you are doing exactly what you should, as your base unit is days. What you really are asking for is related to time (not days), so we need to decide how you represent that. Is it hours between only working days, working hours between working days, etc.
A sample file showing what you have with formula and a page or cell with what you want it to display (calculated manually) would help us help you. As much background and explanation as you can will also make it faster for us to help.
Ways to get help: Post clear questions, explain your overall goal, supply as much background as possible, respond to questions asked of you by those trying to help and post sample(s) files.
"I am here to help, not do it for people" -Me
Start date might not always be a working day, but the end date will always be.
For reference: https://docs.google.com/spreadsheets...it?usp=sharing
1. Starting time of normal working day?
2. Finishing time f normal working day?
3. Total hours between A1 and B1?? Or working hours between A1 and B1?
And finally, is this for Google sheets or Excel (you did not post in the "other platforms" section, but show Google sheets in your profile)?
Hi Glenn,
Thank you for trying to help out.
It might be tricker than I imagined, since everyone works different hours.. I was hoping to simply show the decimal point between the two cells.
This is for Google Sheets, and it's actually my first time here - so I did not know to post somewhere else.
I will just try to find another way.. thank you all of guys for the help.
Assuming working day is 09:00 to 17:00 try this formula in C1 copied down
=NETWORKDAYS(A1,B1)-1+(MEDIAN(MOD(B1,1),"17:00","9:00")-MEDIAN(NETWORKDAYS(A1,A1)*MOD(A1,1),"9:00","17:00"))/("17:00"-"9:00")
Adjust day start and end times as required
This works in Excel and google sheets
Last edited by daddylonglegs; 01-08-2018 at 03:15 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks