Date Calculations

wawezzer

New Member
Joined
Feb 28, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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

SnipImage.JPG
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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).
 
Upvote 0
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. :)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top