 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / September 2007consecutive bi-monthly dates
Is there a way to consecutively add bi-monthly dates to a spreadsheet? Kind of different because the 15th will always be 15. It's the last day of the month that changes each month. This would be for people who are paid bi-monthly so the dates don't have to be input each time. If you've got 15 Sep 07 in A1, then in A2 put =DATE(YEAR(A1),MONTH(A1)+1,0) which will give the last day of the month. In A3 put =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)) to give 15th of the next month. Select A2 and A3, and fill down.  SignatureDavid Biddulph > Is there a way to consecutively add bi-monthly dates to a spreadsheet? > Kind > of different because the 15th will always be 15. It's the last day of the > month that changes each month. This would be for people who are paid > bi-monthly so the dates don't have to be input each time. Enter this in A1 & fill down to A24: =IF(MOD(ROW(),2)=1,DATE(2007,(ROW()+1)/2,15),DATE(2007,ROW()/2+1,0)) > Is there a way to consecutively add bi-monthly dates to a spreadsheet? > Kind > of different because the 15th will always be 15. It's the last day of the > month that changes each month. This would be for people who are paid > bi-monthly so the dates don't have to be input each time. You can use the DATE function. Inputting a 0 for the day, will result in the last day of the previous month. So, for example, your starting data is in A1. In A2 enter: =IF(DAY(A1)=15,DATE(YEAR(A1),MONTH(A1)+1,0),DATE(YEAR(A1),MONTH(A1)+1,15)) HTH, Elkar > Is there a way to consecutively add bi-monthly dates to a spreadsheet? Kind > of different because the 15th will always be 15. It's the last day of the > month that changes each month. This would be for people who are paid > bi-monthly so the dates don't have to be input each time. One way: Enter the first date in cell A1. Either the 15th or the last date of that month. Like this: A1 = either 1/15/2007 or 1/31/2007 Enter this formula in A2 and copy down as needed: =IF(DAY(A1)=15,DATE(YEAR(A1),MONTH(A1)+1,0),A1+15) SignatureBiff Microsoft Excel MVP > Is there a way to consecutively add bi-monthly dates to a spreadsheet? > Kind > of different because the 15th will always be 15. It's the last day of the > month that changes each month. This would be for people who are paid > bi-monthly so the dates don't have to be input each time. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.