Date Format Problem

[prkhan56]

Hello All,

I am using Windows XP/Office 2003 and have the following problems:

I am making an attendance sheet where in Cell C4 is having value
(01/09/2005) showing Sep-2005 (formatted as mm-yyyy)

From A5 downwards I wish to have the following

A5 : 9-1 Thursday
A6 : 9-2 Friday
A7 : 9-3 Saturday

I have entered the following formula in A5

=MONTH(C4)&-DAY(C4)&TEXT(C4,"DDDD") which gives me the desired result
as
9-1 Thursday.

My problem is when I drag it down it shows me values which I don't
want...How can I drag and just have Day and the Weekday incremented as
follows:

A6 : 9-2 Friday
A7 : 9-3 Saturday
A8 : 9-4 Sunday


Any better suggestions or help would be appreciated.

TIA

Rashid Khan

 

[Mike]

copy c4 down as a workaround

Hello All,

I am using Windows XP/Office 2003 and have the following problems:

I am making an attendance sheet where in Cell C4 is having value
(01/09/2005) showing Sep-2005 (formatted as mm-yyyy)


A5 : 9-1 Thursday
A6 : 9-2 Friday
A7 : 9-3 Saturday

I have entered the following formula in A5

=MONTH(C4)&-DAY(C4)&TEXT(C4,"DDDD") which gives me the desired result
as
9-1 Thursday.

My problem is when I drag it down it shows me values which I don't
want...How can I drag and just have Day and the Weekday incremented as
follows:

A6 : 9-2 Friday
A7 : 9-3 Saturday
A8 : 9-4 Sunday


Any better suggestions or help would be appreciated.

TIA

Rashid Khan

 

[Ron Rosenfeld]

Hello All,

I am using Windows XP/Office 2003 and have the following problems:

I am making an attendance sheet where in Cell C4 is having value
(01/09/2005) showing Sep-2005 (formatted as mm-yyyy)


A5 : 9-1 Thursday
A6 : 9-2 Friday
A7 : 9-3 Saturday

I have entered the following formula in A5

=MONTH(C4)&-DAY(C4)&TEXT(C4,"DDDD") which gives me the desired result
as
9-1 Thursday.

My problem is when I drag it down it shows me values which I don't
want...How can I drag and just have Day and the Weekday incremented as
follows:

A6 : 9-2 Friday
A7 : 9-3 Saturday
A8 : 9-4 Sunday


Any better suggestions or help would be appreciated.

TIA

Rashid Khan

A5: =C4

Format/Cells/Number/Custom Type: m-d dddd
A6: =A5+1

Select A6 and drag down.

I would suggest to Right-Justify for appearance.


--ron

 

[Dave Peterson]

How about just putting the date in those cells and using a custom format:

m-d dddd
format|Cells|Number tab|custom category

Or I like:
mm-dd* dddd

You can widen the column and the cell appears right and left justified. (And I
like 2 digit months/days.)

 

[prkhan56]

Thanks to Dave and Ron

Works great. You guys are a real help for us.

Thanks once again

Rashid Khan

 

[prkhan56]

Thanks to Dave and Ron

Works great. You guys are a real help for us.

Thanks once again

Rashid Khan

 

[prkhan56]

Hi Dave,
A follow up on my previous post

Is there any way I can Highlight all the Rows where "Friday" is
displayed?

I tried =FIND("Friday",$A1) in Conditional Formatting...but it does not
work

Can u suggest something please? How can I highlight all rows where
Friday is shown using the Custom Format of mm-dd dddd

Thanks for your help in advance

Rashid Khan

 

[Ron Rosenfeld]

Hi Dave,
A follow up on my previous post

Is there any way I can Highlight all the Rows where "Friday" is
displayed?

I tried =FIND("Friday",$A1) in Conditional Formatting...but it does not
work

Can u suggest something please? How can I highlight all rows where
Friday is shown using the Custom Format of mm-dd dddd

Thanks for your help in advance

Rashid Khan

Highlight your column of Dates.

Format/Conditional Formatting Formula Is:

=WEEKDAY(A1)=6

Format to taste


--ron

 

[prkhan56]

Hi Ron,
Thanks for the quick reply.

It works fine.. but I wish to highlight the entire rows where the
condition is true...

Any suggestions.

Rashid Khan

 

[Ron Rosenfeld]

Hi Ron,
Thanks for the quick reply.

It works fine.. but I wish to highlight the entire rows where the
condition is true...

Any suggestions.

Rashid Khan

Change the formula to

=WEEKDAY($A1)=6

Then use the format painter to copy the format to the cells you wish. The
reference will adjust automatically.


--ron

 

[prkhan56]

Thanks Ron,

Works great....


Rashid Khan

 

[Ron Rosenfeld]

Thanks Ron,

Works great....


Rashid Khan

You're welcome. Glad to help.
--ron