Date calculations

  • Thread starter Thread starter Sarah Petterson
  • Start date Start date
S

Sarah Petterson

In fixed assets we use a mid-month convention when
calculating depreciation. This means that any assets with
a date in service on the 1st throuth the 15th of the month
has a prorate date of the first of the month of the
service month. Any asset with a date on the 16th through
the end of the month has a prorate date of the first of
the next month. Ie: 1/15/03 = 1/1/03 and 1/22/03 =
2/1/03. I am trying to write a formula that will
calculated the prorate date from the date in service.
Anyone have any ideas? Thanks.
 
Sarah

assuming the date is in cell E1,

=IF(DAY(E1)<=15,DATE(YEAR(E1),MONTH(E1),1),DATE(YEAR(E1),MONTH(E1)+1,1))

Format the cell as a date, for example, "dd/mm/yyyy"

Regards

Trevor
 
In fixed assets we use a mid-month convention when
calculating depreciation. This means that any assets with
a date in service on the 1st throuth the 15th of the month
has a prorate date of the first of the month of the
service month. Any asset with a date on the 16th through
the end of the month has a prorate date of the first of
the next month. Ie: 1/15/03 = 1/1/03 and 1/22/03 =
2/1/03. I am trying to write a formula that will
calculated the prorate date from the date in service.
Anyone have any ideas? Thanks.
Click to expand...

If the Date in Service is in A1, then:

=DATE(YEAR(A1),MONTH(A1)+(DAY(A1)>=16),1)

Does the same 16th day rule apply to February?


--ron
 
Back
Top