SOLVED

Subtract a Date from the Previous Date in a Table Column Using Structural References

%3CLINGO-SUB%20id%3D%22lingo-sub-2221418%22%20slang%3D%22en-US%22%3ESubtract%20a%20Date%20from%20the%20Previous%20Date%20in%20a%20Table%20Column%20Using%20Structural%20References%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2221418%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20there%20a%20formula%20or%20function%20I%20can%20use%20to%20subtract%20one%20date%20from%20the%20previous%20date%20in%20the%20same%20column%20to%20get%20the%20number%20of%20days%20since%20the%20previous%20date%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20do%20this%20in%20a%20way%20that%20the%20formula%20doesn't%20crash%20when%20I%20delete%20a%20row%20in%20the%20middle%2C%20hence%20looking%20at%20structural%20references.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20also%20need%20to%20make%20sure%20that%20the%20formula%20doesn't%20get%20an%20error%20when%20calculating%20the%20difference%20for%20the%20very%20first%20date%20in%20the%20column%20since%20it%20would%20try%20to%20subtract%20the%20column%20header%20from%20the%20first%20date.%26nbsp%3B%20The%20formula%20I%20have%20in%20column%20E%20takes%20care%20of%20the%20error%20catching%2C%20but%20not%20the%20deletion%20of%20rows.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20someone%20help%20me%20with%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESpreadsheet%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%3C%2FP%3E%3CP%3EGeorge%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2221418%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2222000%22%20slang%3D%22en-US%22%3ERe%3A%20Subtract%20a%20Date%20from%20the%20Previous%20Date%20in%20a%20Table%20Column%20Using%20Structural%20References%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2222000%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F990681%22%20target%3D%22_blank%22%3E%40George_Weston%3C%2FA%3E%26nbsp%3BMy%20Excel%20didn't%20like%20your%20file%20and%20could%20not%20open%20it.%20So%2C%20I%20created%20an%20example%20in%20a%20blank%20file%20as%20shown%20in%20the%20picture.%20Should%20work%20similar%20in%20your%20situation.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-03-19%20at%2008.34.10.png%22%20style%3D%22width%3A%20459px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F265435iFA81D2552B83B5A3%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-03-19%20at%2008.34.10.png%22%20alt%3D%22Screenshot%202021-03-19%20at%2008.34.10.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Is there a formula or function I can use to subtract one date from the previous date in the same column to get the number of days since the previous date?

 

I need to do this in a way that the formula doesn't crash when I delete a row in the middle, hence looking at structural references.  

 

I also need to make sure that the formula doesn't get an error when calculating the difference for the very first date in the column since it would try to subtract the column header from the first date.  The formula I have in column E takes care of the error catching, but not the deletion of rows.

 

Can someone help me with this?

 

Spreadsheet attached.

 

Thank you,

George

3 Replies
best response confirmed by George_Weston (Occasional Contributor)
Solution

@George_Weston My Excel didn't like your file and could not open it. So, I created an example in a blank file as shown in the picture. Should work similar in your situation.

Screenshot 2021-03-19 at 08.34.10.png

@George_Weston 

That could be

=IF(ROW()=ROW(Table9[[#Headers],[Date]])+1, "",
    [@Date]-INDEX([Date],ROW()-ROW(Table9[[#Headers],[Date]])-1))
Thank you Riny. It is a simple elegant solution.
Regards,
George