Aging report formula that does NOT use 'TODAY' but rather March 6 2020

%3CLINGO-SUB%20id%3D%22lingo-sub-1210143%22%20slang%3D%22en-US%22%3EAging%20report%20formula%20that%20does%20NOT%20use%20'TODAY'%20but%20rather%20March%206%202020%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1210143%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I%20have%20created%20a%2013%20week%20aging%20report%20for%20a%2013%20week%20cash%20flow.%26nbsp%3B%20I%20am%20currently%20using%20%3DIF(AND(TODAY()-%24C486%26lt%3B%3D21%2CTODAY()-%24C486%26gt%3B14)%2C%24D486%2C0)%20formula%2C%20does%20anyone%20know%20a%20formula%20that%20can%20be%20used%20for%20aging%20reports%2C%20but%20does%20not%20use%20%22TODAY'%20as%20the%20starting%20point%20to%20age%3F%26nbsp%3B%20I%20have%20a%20rather%20large%20spread%20sheet%20and%20I%20cannot%20get%20my%20reports%20done%20in%20one%20day%20so%20when%20I%20come%20back%20to%20it%20the%20next%20day%20or%20in%20a%20few%20days%2C%20some%20of%20my%20values%20in%20the%20columns%20have%20moved%20by%20one%20column.%26nbsp%3B%20Is%20there%20a%20way%20I%20can%20say%20age%20from%20March%206th%202020%2C%20not%20today%20but%20from%20a%20static%20date.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3CP%3ELorna%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1210143%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1210298%22%20slang%3D%22en-US%22%3ERe%3A%20Aging%20report%20formula%20that%20does%20NOT%20use%20'TODAY'%20but%20rather%20March%206%202020%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1210298%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F574674%22%20target%3D%22_blank%22%3E%40Pasco2018%3C%2FA%3E%26nbsp%3B-%3C%2FP%3E%3CP%3EIf%20I%20understand%20correctly%2C%20I%20offer%20two%20options%3A%3C%2FP%3E%3CP%3EOption1%20uses%20a%20helper%20field%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIF(AND(%24A%242-D2%26lt%3B%3D21%2C%24A%242-D2%26gt%3B14)%2CTRUE%2CFALSE)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EOption2%20does%20not%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIF(AND(DATEVALUE(%223%2F6%2F2020%22)-D2%26lt%3B%3D21%2CDATEVALUE(%223%2F6%2F2020%22)-D2%26gt%3B14)%2CTRUE%2CFALSE)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Snag_2f72f51.png%22%20style%3D%22width%3A%20535px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F175257i4C10063C11B697F8%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Snag_2f72f51.png%22%20alt%3D%22Snag_2f72f51.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello, I have created a 13 week aging report for a 13 week cash flow.  I am currently using =IF(AND(TODAY()-$C486<=21,TODAY()-$C486>14),$D486,0) formula, does anyone know a formula that can be used for aging reports, but does not use "TODAY' as the starting point to age?  I have a rather large spread sheet and I cannot get my reports done in one day so when I come back to it the next day or in a few days, some of my values in the columns have moved by one column.  Is there a way I can say age from March 6th 2020, not today but from a static date.

 

Thank you

Lorna

1 Reply

@Pasco2018 -

If I understand correctly, I offer two options:

Option1 uses a helper field:

=IF(AND($A$2-D2<=21,$A$2-D2>14),TRUE,FALSE)

Option2 does not:

=IF(AND(DATEVALUE("3/6/2020")-D2<=21,DATEVALUE("3/6/2020")-D2>14),TRUE,FALSE)

 

Snag_2f72f51.png