Trying to calcuated time elasped on a file that arrived at 16:04:42 PM on a Friday and was not returned to the customer until 9:47:00 AM on the following Monday.
Business Hours
Mon-Fri 6:00 AM to 5:00 PM
Saturday 9:00 AM to 5:00 PM
Thanks in advance for your help.
Upload a file (go advanced>Manage Attachments) which shows how your data is laid out. Is date and time in the same cell or different cells? Is it definitely formatted as numbers or as text? It would help to see an example spreadsheet.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
(Friday Close - Received) + Saturday + (Sent - Monday Open) = 12:42:18
Assuming you have start time/date in A2 and end time/date in B2 try this formula in C2 for elapsed business hours
=(SUM(NETWORKDAYS.INTL(A2,B2,{1,"1111101"})*{11,8})-IF(WEEKDAY(A2)=7,8,11))/24+MOD(B2,1)-MOD(A2,1)
Custom format C2 as [h]:mm
I'm assuming that the times/dates will always be within working hours
Audere est facere
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks