Returning Values for Date Intervals

[lager1001]

I need some help with a formula and I've tried searching but not finding an exact match to what I'm looking for.

Column A has hundreds of dates, past, present(today), and future.

I need a formula that will tell me how many days old (by interval) any past dates are. The intervals would be 0-5 Days, 6-10 Days, 11-15 Days, 16-20 Days, 21-25 Days, 26-30 Days, 31-35 Days, 36-40 Days, 41-45 Days, etc. Anything AFTER today would just return the value of "Future Order").

Thank you for any help!

 

[Eric W]

How about:

Book1
	A	B
1	Date	Interval
2	1-Jan	276-280 Days
3	1-May	156-160 Days
4	1-Sep	31-35 Days
5	15-Sep	16-20 Days
6	26-Sep	6-10 Days
7	1-Oct	1-5 Days
8	7-Oct	Today
9	1-Dec	Future Order
Sheet1

Cell Formulas
Range	Formula
B2	=IF(A2),MROUND(TODAY()-A2,5)-4&"-"&MROUND(TODAY()-A2,5)&" Days",IF(A2=TODAY(),"Today","Future Order"))

 

[Eric W]

Looks like I misunderstood how MROUND works. Try this instead:

Book1
	A	B
1	Date	Interval
2	1-Jan	276-280 Days
3	1-May	156-160 Days
4	1-Sep	36-40 Days
5	15-Sep	21-25 Days
6	26-Sep	11-15 Days
7	1-Oct	6-10 Days
8	7-Oct	Today
9	1-Dec	Future Order
Sheet1

Cell Formulas
Range	Formula
B2	=IF(A2),ROUNDUP((TODAY()-A2)/5,0)*5-4&"-"&ROUNDUP((TODAY()-A2)/5,0)*5&" Days",IF(A2=TODAY(),"Today","Future Order"))

 

[lager1001]

This is perfect. Thank you so much!

 

[Fluff]

Or with Eric's original formula
=IF(A2<TODAY(),MROUND(TODAY()-A2+2,5)-4&"-"&MROUND(TODAY()-A2+2,5)&" Days",IF(A2=TODAY(),"Today","Future Order"))

which seems to work.

 

[Eric W]

Glad we could help!

Fluff, it looks like part of your formula got cut off, but it appears to be a clever fix to my original formula.

 

[Fluff]

It' the dreaded < again, it should be
=IF(A2< TODAY(),MROUND(TODAY()-A2,5)-4&"-"&MROUND(TODAY()-A2,5)&" Days",IF(A2=TODAY(),"Today","Future Order"))

Iv'e never come across the Mround function before, so thought I'd have a play with it

 

[Eric W]

This actually reminded me of one of Bill's (Mr. Excel) tips:

https://www.mrexcel.com/excel-tips/round-up-to-the-next-20/

Once I looked it up, it turns out that CEILING or CEILING.MATH would also work.