Displaying the Date #3

H

Henry

I'm trying to display the date, under the days of the week. But instead of
starting with sun and going thru sat. I'm setting the spread sheet up for a
Fri, Sat, and Sun only. Any help would be greatly appreciated!

Thanks,

Henry
 
M

Max

I'm trying to display the date, under the days of the week.
But instead of starting with sun and going thru sat.
I'm setting the spread sheet up for a Fri, Sat, and Sun only.
Click to expand...

One way ..

For filling across, starting with a Friday, say 3 Mar 2006:

Put in say, B2:
=DATE(2006,3,3)+COLUMNS($A:A)-1+INT((COLUMNS($A:A)-1)/3)*4
Format B2 as date to taste, then copy B2 across as far as required

B2:D2 returns 3 Mar - 5 Mar 2006 (Fri - Sun),
E2:G2 returns 10 Mar - 12 Mar 2006 (Fri - Sun)
and so on across

To fill down, starting with a Friday, say 3 Mar 2006:

Put in say, A3:
=DATE(2006,3,3)+ROWS($A$1:A1)-1+INT((ROWS($A$1:A1)-1)/3)*4
Copy A3 down as far as required

A3:A5 returns 3 Mar - 5 Mar 2006 (Fri - Sun),
A6:A8 returns 10 Mar - 12 Mar 2006 (Fri - Sun)
and so on down

The above expression is similar to that for filling across,
except that ROWS replaces COLUMNS as the incrementer

DATE(2006,3,3) = 3 Mar 2006 (a Friday)
[ DATE(year,month,day) ]

Adapt the starting date to suit ..
 
M

Max

Put in say, B2:
=DATE(2006,3,3)+COLUMNS($A:A)-1+INT((COLUMNS($A:A)-1)/3)*4
Format B2 as date to taste, then copy B2 across as far as required
Click to expand...

And to label the "day" for row2 (i.e. B2 across) above in row1, say,
just place in B1: =TEXT(B2,"ddd"), and copy B1 across
This will label B1 across with: Fri, Sat, Sun, Fri, Sat, Sun, Fri, Sat, Sun

Likewise if we want to label for col A,
we could put in B3, and copy down: =TEXT(A3,"ddd")
 
Top