Is there a way to automaically include bi-monthly paydates in a formula for a calendar generated in Excel?
Thank you for your time...
Pay dates on a calendar - formula
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Pay dates on a calendar - formula
To calculate bi-monthly dates you can use a formula similar to this:
=DATE(YEAR(A1),MONTH(A1),IF(DAY(A1)<15,1,15))
Use the DATE function for year and month, then an IF statement to determine if "1" or "15" is the day of the payment.
Substitute the reference A1 in the formula for the cell on the calendar (or the date on the sheet)
To project the date forward you can add one to the month: ...,MONTH(A1)+1,...
=DATE(YEAR(A1),MONTH(A1),IF(DAY(A1)<15,1,15))
Use the DATE function for year and month, then an IF statement to determine if "1" or "15" is the day of the payment.
Substitute the reference A1 in the formula for the cell on the calendar (or the date on the sheet)
To project the date forward you can add one to the month: ...,MONTH(A1)+1,...
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- NewLounger
- Posts: 9
- Joined: 21 Aug 2014, 18:33
Re: Pay dates on a calendar - formula
Thank you very much for your assistance.