Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
consecutive 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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
consecutive bi-monthly dates
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. -- David Biddulph "dmr" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
consecutive bi-monthly dates
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)) "dmr" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
consecutive bi-monthly dates
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(Y EAR(A1),MONTH(A1)+1,15)) HTH, Elkar "dmr" wrote: 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
consecutive bi-monthly dates
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) -- Biff Microsoft Excel MVP "dmr" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I sort a column of random dates into Consecutive dates | Excel Worksheet Functions | |||
Printing consecutive dates | Excel Discussion (Misc queries) | |||
Determining consecutive dates? | Excel Worksheet Functions | |||
Assigning consecutive dates | Excel Worksheet Functions | |||
Count consecutive dates only | Excel Discussion (Misc queries) |