Hello everyone. I am trying to calculate the amount of lapsed time between two dates and times. Each date and time combo are combined into one cell. I also need to take into account for business days and stock market hours (9:30am-4:00pm). I am having problems combining the NETWORKDAY formula with time within the same cell. Can someone PLEASE help? I've attached a spreadsheet with what I'm dealing with. Thank you very much.
Assuming that "Entry time and date" and "Exit time and date" will always be within the working hours use this formula in U13 for elapsed work hours
=MAX(0,(NETWORKDAYS(C13,E13,Z$14:Z$200)-1)*W$15+MOD(E13,1)-MOD(C13,1))
format as [h]:mm and for your example you'll get 0:07. it will work over any period of time
Audere est facere
Hello daddylonglegs. Thank you very much! Repped.Originally Posted by daddylonglegs
This seems to work for intra-day trades, but I don't think it is returning the correct value for multiple day computations. For example, please see attached. I updated the entry date to a few days earlier, and the value is still sub 1. Any ideas?
15/04/2000 was a Saturday, is it valid for the entry date to be a Saturday? If so then you need a different formula, if not then try making it 14/04/2000 09:30 and custom format result cell as [h]:mm you'll get 6:37 which is correct for Friday to Monday
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks