RANK dates from the middle of the month.

[Paul Moles]

I am paid on the 15th of each month, my bills are spread throughout the month.
I am trying to find a formula or system to automatically rank the payments
as they occur.
At the moment I use an adjacent column to manually order the payments, and
then sort on this column.
The problem of course is that an expense on the 16th occurs before one on
the 28th but also before those on the 14th. (I don't get paid again until
15th)
Surely there is a simple formula to get the adjacent column to automatically
work out the order of payment. Without me having to renumber them all when a
payment date changes.

Many Thanks
Paul Moles

 

[Sheeloo]

With your dates in Col B (date only like 1, 2, 3 etc.) starting at B2
use this in B2 and copy down
=IF(B2<15,B2+15,MOD(B2,15))
it will give you 0 for 15, 1 for 16,..., 16 for 1, ... and so on.

 

[Sheeloo]

With your dates in Col B (date only like 1, 2, 3 etc.) starting at B2
use this in B2 and copy down
=IF(B2<15,B2+15,MOD(B2,15))
it will give you 0 for 15, 1 for 16,..., 16 for 1, ... and so on.