Step 1. Excel looks at days as a single unit of measure, i.e. 2/13/2011 + 1 = 2/14/2011. To convert to minutes, multiply by 24 * 60 or 1440 and format as General.
how long are your time intervals? Is this like an employee timesheet and some of the times are 3rd shift (over midnight)? Or are we talking about projects and the times can span days?
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
Thank you :O) Step 1 works perfectly now and makes so much sense now someone has pointed it out !
Step 2 - It'll sometimes span days between the start and end
If the start date/time and end date time will always be within opening hours you can use this formula to calculate time within opening hours
=(NETWORKDAYS(A2,B2)-1)*("18:00"-"8:00")+MOD(B2,1)-MOD(A2,1)
where A2 is start date/time and b2 end date/time
format result cell as [h]:mm to get a result like 8:30
If you want that in minutes (510) then use
=((NETWORKDAYS(A2,B2)-1)*("18:00"-"8:00")+MOD(B2,1)-MOD(A2,1))*1440
format result cell as general
Audere est facere
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
joja 
Register To Reply
Bookmarks