I was trying to find first previous entry of text (“Mits”) in column B and return a cell address that I could use w/ OFFSET to grab the date from one cell up and to right (column C) of text then use DAY function to compute elapsed number of days between occassional reoccurances of "Mits" in the B column – but maybe I’m totally barking up the wrong tree so I will explain what I’m trying to accomplish.
My spreadsheet tracks a recurring process. User inputs dates, weights & time for each batch in color-coded cells - spreadsheet tracks days between batches, output weights (pounds, gallons, %, time & totals) in non-colored cells. To keep information compact (& allow occasional double batch inputs) I use 2 rows for each batch with some upper/lower cells merged (mostly user input cells), then display output data in upper and lower cells.
Main area of question involves:
Column B is unmerged – upper cell automatically tracks days between batches, lower cell allows input for occasional text indicating non-normal batch source OR automatically tracks days between 1st & 2nd drum used in occassional double batch.
Column C is unmerged – top cell is source date of drum for single batch, bottom cell is only used occasionally for 2nd drum source date if a double batch is run.
As a beginner my formula is probably messy, but using DAY function I managed to take date from previous batch source “C9”and subtract from next batch “C11” to track elapsed days between source batches. Then if previous batch was from source “Mits” it skips up to previous batch before “Mits” batch “C7” for date, then subtracts. If no date is entered in column C then cell in column B is left blank. The formula used:
=IF(C11>0,IF(EXACT(B10,"Mits"),DAY(C11-C7),DAY(C11-C9)),"")
The following will likely make sense only if looking at attached spreadsheet:
I haven’t updated formula to accomplish this yet, but eventually, if I run a double batch from standard recurring source (it wouldn’t need source ID like “Mits” entered in lower “B” cell) the 2 cells in “C” column would track both source dates – upper one (row 21) the older source and lower cell (row 22) the newest source, then the upper cell in B column (B21) displays elapsed time in days between previous batch (C19) & 1st drum (C21) while lower cell (B22) would track elapsed time between 1st & 2nd source drums (C21 & C22) for double batch.
Don't know if this affects anything:
I use conditional formatting to color cells of “Mits” batch orange, and may possibly use 2nd or 3rd non-normal sources in future that would be another color and need to search for and subtract those dates separately.
Bookmarks