Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
RANK dates from the middle of the month.
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
RANK dates from the middle of the month.
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. "Paul Moles" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Middle date of two dates | Excel Discussion (Misc queries) | |||
remove dates is the middle of a line | Excel Discussion (Misc queries) | |||
Date in middle of month | Excel Discussion (Misc queries) | |||
Rounding up at the middle of the month. | Excel Discussion (Misc queries) | |||
Rank items, select one start date, have remaining dates follow based on rank | Excel Discussion (Misc queries) |