Number of days depending on period time frame and contract time frame

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3152287%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3ENumber%20of%20days%20depending%20on%20period%20time%20frame%20and%20contract%20time%20frame%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3152287%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CP%3EHi%2C%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EI%20want%20to%20display%20the%20number%20of%20days%20for%20an%20interest%20calculation.%26nbsp%3B%20I%20also%20want%20the%20formula%20to%20take%20into%20account%20the%20contract%20start%20and%20end%20dates%20as%20sometimes%20the%20quarter%20has%20a%20contract%20either%20starting%20or%20ending%20mid%20way%2C%20the%20contract%20may%20not%20have%20started%20or%20may%20be%20ended%20before%20or%20after%20the%20quarter%20so%20then%20the%20%23%20of%20days%20should%20be%20nil.%26nbsp%3B%20The%20formula%20will%20need%20to%20consider%20each%20of%20the%20options%20and%20decide%20the%20correct%20number%20of%20days%20to%20accrue%20the%20interest.%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EExamples%20of%20the%20correct%20values%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%5C%26quot%3Blia-inline-image-display-wrapper%22%20lia-image-align-inline%3D%22%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Fgxcuf89792%2F%5C%26quot%3Bhttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F347177iF5FFAB2DDE31B756%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%5C%26quot%3B%22%20role%3D%22%5C%26quot%3Bbutton%5C%26quot%3B%22%20title%3D%22KT123530_0-1644539042867.png%22%20alt%3D%22%5C%26quot%3BKT123530_0-1644539042867.png%5C%26quot%3B%22%20%2F%3E%26lt%3B%5C%2Fspan%26gt%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3152287%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EExcel%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E
New Contributor

Hi,

I want to display the number of days for an interest calculation.  I also want the formula to take into account the contract start and end dates as sometimes the quarter has a contract either starting or ending mid way, the contract may not have started or may be ended before or after the quarter so then the # of days should be nil.  The formula will need to consider each of the options and decide the correct number of days to accrue the interest.

Examples of the correct values

KT123530_0-1644539042867.png

 

2 Replies

@KT123530 This would give you the numbers you're asking for:

JMB17_1-1644541246760.png

 

But, I don't think it's quite right - I would think the third and fourth entries should be 59 and 56 days (2/1/22-3/31/22 and 2/1/22-3/28/22).

 

If that's correct, then perhaps this is what you want:

JMB17_2-1644541531748.png

 

@JMB17 

Thank you very much - this has stumped me for a while and i keep having to do work arounds.  A lot simpler than I anticipated!