I fixed my problem with Excell, now I have another question.

[hedgehog]

I found out that if yu check the "high contrast" button the Accesibility Options in the control panel, it locks the screen image in Excell to black type on a white background. Your cells will print in any color you slect, but you won't see the color selections on the monitor.


Now, my question:

I often do spread sheets based on pay periods which would have dates set for the 15 and last of each month. Is there a way to generate the fill for this? The best I can do is get Excell to insert every 15 days, which soon gets off-track.

I want this: 1/15/10, 1/31/10, 2/15/10, 2/28/10, 3/15/10, 3/31/10 etc.

I get this: 1/15/10, 1/30/10, 2/13/10, 2/28/10, 3/15/10, 3/30/10 etc

[pokerfan]

1. I have used a workaround

It's not elegant but it works.

I fill one column with all the fifteenths and another column with all the 'lasts.' (If you start with a 31 month, OpenOffice figures out the shorter months - I expect excell will too.)

Then I cut/paste one column into the other.

Then I run a sort on the column.

Presto!

[Dead_Parrot]

2. This should do the trick:

=if(day(a1)=15,(eomonth(a1,0),a1+16)

So, assuming A1 is the previous/first date in the series:
>If the previous date is the 15th, find the last day of that month;
>>Otherwise, we assume it's the end of the month - add 16 days to find the 15th of the following month.

If you need this for 2000 or earlier (ie, no 'eomonth' function) let me know - it's fiddly but do-able.

Note that it will probably forget it's dealing with dates, and chuck the date serials out (numbers around 40,000 at the moment) - format the cells to 'date' manually to restore sanity.

[Dead_Parrot]

3. Oops

Sorry, that should be: =if(day(a1)=15,eomonth(a1,0),a1+16) - no ( before the eomonth call. Brain not connected to fingers. :)

[struggle4progress]

4. You can do something like this using a nested if:

Say column D represents one of 24 semimonthly payment periods in the year:
Set D2 to 1
Put =IF(D2 <24,D2+1,1) in D3 and drag down to fill the column 1, 2, 3, 4, 5 , ... , 23, 24, 1 , ...

Say column E represents one of 12 months in the year:
Set E2 to 1
Put =ROUND(D2/2,0) in E3 and drag down to fill the column 1, 1 , 2, 2, 3, .... 11, 12, 12 , 1 , ...

Say column F represents one of 24 monthly pay days in the year
-- we'll get to the rest of this in a minute

Say column G represents the year:
Set G2 to 2
Put =IF(D2<24,G2,G2+1) in G3 and drag down to fill the column 2010, 2010, ... 2010, 2011, 2011, ...

Now finish with column F
Put
=IF(MOD($D2,2)=1,15,IF(OR(AND($E2<8,MOD($E2,2)=1),AND($E2>7,MOD($E2,2)=0)),31,IF($E2=2,IF(MOD(G2,4)=0,29,28),30)))
into F2 and drag down to fill the column 15, 31, 15, 28, 15, 31, ...

Say column H represents the date
Put =DATE(G2,E2,F2) in H2 and drag down to fill the column

I did this in "Numbers" but something entirely similar should work in excel