Date Calculations

[wawezzer]

Hi Clever Excel World,

I have a sales sheet which in one column I have dates (the sales were made). When the sale has been processed and ordered we take this off the sheet but sometimes sales remain on there for a length of time.

in another column I need to calculate the days that its been left dormant (almost) so I have a cell reading 10 days Old, 20 Days old, 30 days old and 1 saying 40+. I've tried a few calculations but its not throwing anything through

For example 10 (the number only) orders showing in 10 days old and the following day 3 might go in to 20 days (due to the date reached) example attached

 

[jasonb75]

As far as I can see from your screen capture, this should do the 10-19 day window for John in January.

=COUNTIFS($B$8:$B$11,"<="&TODAY()-10,$B$8:$B$11,">"&TODAY()-20)

You will need to write the same formula for each group of red cells and add them together, then repeat for the other date ranges.

Because of the way that your sheet has been designed, a simple formula is not going to work (merged cells, currency amounts in the same columns as the dates, possibly other things not visible from a screen capture).

 

[wawezzer]

As far as I can see from your screen capture, this should do the 10-19 day window for John in January.

=COUNTIFS($B$8:$B$11,"<="&TODAY()-10,$B$8:$B$11,">"&TODAY()-20)

You will need to write the same formula for each group of red cells and add them together, then repeat for the other date ranges.

Because of the way that your sheet has been designed, a simple formula is not going to work (merged cells, currency amounts in the same columns as the dates, possibly other things not visible from a screen capture).

It worked! Yay, thank you.

 

[jasonb75]

You're welcome
Actually, I just noticed something that might make the formula simpler.

Your columns appear to be in the order of 'Date, Number, Blank, Text, Currency, Currency' as long as the column that has text now is the only one that will have text, then this might work without having to add each group of cells individually.

=COUNTIFS($B:$N,"<="&TODAY()-10,$B:$N,">"&TODAY()-20,$E:$Q,"?*")

What you could find though, is that if you enter text into column J that the currency amount in column G might be counted as a date >40 days.