Hi, experts,
I need to find missing hours from the date and hour range.
There are a lot of tricks (in the internet) how to do it it with the missing numbers, however none of them works with hours.
Is it possible to do this with formula the way given in the attached sample file?
i have attached a sample using two different methods, EXCEL 2010+ (using AGGREGATE), and EXCEL <=2007 (using SMALL-IF-ISNA array)
i hope it solves the issue
sample formula using AGGREGATE:
sample formula using an array (CTRL+SHIFT+ENTER)
Please Login or Register to view this content.
Please Login or Register to view this content.
Last edited by janmorris; 08-15-2021 at 04:26 AM.
Sorry for off-topic interjection:
Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.
I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Without helper column.
ARRAY formula in D2 copied down.
To enter ARRAY formulaPlease Login or Register to view this content.
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
With HELPER column F
In F2 copied down
=IF(($A$2+(ROW($A1)-1)/24)<=$A$16,$A$2+((ROW($A1)-1)/24),"")
In G2 copied down
=IFERROR(INDEX($F:$F,AGGREGATE(15,6,ROW($F$2:$F$22)/(COUNTIF($A$2:$A$16,$F$2:$F$22)=0),ROWS($G$2:$G2))),"")
Last edited by kvsrinivasamurthy; 08-15-2021 at 05:07 AM.
Good day janmorris and kvsrinivasamurthy.
janmorris - your solution works, thank you.
kvsrinivasamurthy, an option with the MMULT formula works exactly as needed, but another option with the helper extracts hours that are not missing. Pls. see attached file (extra hours marked with red).
Last edited by T.I.; 08-15-2021 at 11:27 AM.
It is because of difference in the decimal places.
Formula changed. Pl see file.
Please try at D2
=IFERROR(INT($A$2)+TIME(AGGREGATE(15,6,ROW($A$1:INDEX($A$1:$A$24,MAX(HOUR($A$2:$A$16))))-1/ISNA(MATCH(ROW($A$1:$A$24)-1,HOUR($A$2:$A$16),)),ROWS(D$2:D2)),,),"")
kvsrinivasamurthy Works great now. Thank you.
Bo_Ry Excellent, like always! Thank you too.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks