Hi All,
Looking for a formula to lookback 24 calendar hours and extract the hours and minutes within the start to end periods.
A B C
Start End 24 hr lookback
10/24/13 12:00 10/24/13 13:20 1:20
10/24/13 18:00 10/24/13 19:10 sum of time within 24 hours of C3 = 2:30 or 2.5
10/24/13 19:40 10/24/13 20:40 Sum of time within 24 hours of C4 = 3:30 or 3.5
10/25/13 12:40 10/25/13 13:40 Sum of time within 24 hours of C5 = 4:10 or 4.2
Maybe.... in C2
=(B2-A2)+IF(ISTEXT(C1),0,C1)
format as time (or as [h]:mm if you will run over 24 hr)
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
Hi Glenn,
Thank you for the quick response.
The result I was looking for in C5 is 3:10 or 3.2. As Row 2 is beyond the 24 hour look back its duration would not be included. Sorry I probably should have explained better.
Any suggestions?
Last edited by teststrip; 11-16-2017 at 01:00 PM. Reason: Attachment
The following formula may be pasted into cell C2 and, when dragged down to C5, will yield the same values as those manually placed in D2:D5Formula:
Please Login or Register to view this content.
*Format cells C2:C5 to display [h]:mm as Glen stated.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
JeteMc and Glenn,
Strong work! Almost there.
I revised the spreadsheet with your formula and added notes. The challenge here is to take only the duration that is within 24 hours of corresponding B. Which maybe only a portion of the time from a previous Row.
Take a look at the attached Book 1 with the notes. I believe its easier explained there.
Any suggestions?
When pasted into D2 and copied down, the following formula yields 7:59 for cell D13:Formula:Please Login or Register to view this content.
I didn't check to see if the other values are what could reasonably be expected, so I encourage closer examination.
Let us know if you have any questions.
JeteMC,
Fantastic...Absolutely perfect. Problem solved.
Thank you!
You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks