How associate a number to a day of the Week

[Ismael]

Hi to all,

This time a think I have a simple doubt for you guys.

So my doubt is how can I associate a number of one day to a day of the week, for example I have in column C of the attachment a register of data and hour, in column I the digit correspondent to the day in question, what I pretend is associate this number to the day of the week, for example for the first value (cell C5) the day of the week is Wednesday, and make the same thing to the others. My original file goes to the day 07/07/05.

If you guys can help me one more time I really appreciate.

Thanks

Best regards,

Ismael

[mdmackillop]

Hi, I'm not sure if this is what you're after.

In J5 enter =Day(I5) or =Day(I5+1) to suit. Format the cell with custom number format "ddd" to return Monday etc.

[Ismael]

Hi,

The formula that you send? me works for the first month, but not for the next ones.

Because, in the cell I5 the number is one, so the formula returns Sunday, I solve this problem by put I5+3, and the formula return Wednesday that is the correct day. So until the end of June everything works fine, the problem happens when we change to July, because the day 1 is Friday, but the formula will return Tuesday (as you can see in the cell I3754 in the attachment file).

So how can I solve this problem??

Thanks

Best regards,

Ismael

[Norie]

Ismael

What day do you actually want to appear in column I?

[sheeeng]

Try have a look this link..
http://msdn.microsoft.com/library/de...fctweekday.asp

HTH

[Ismael]

Hi Norie,

what Ipretend is transform all the numbers in days of the week.

For example, the 4 of June is Saturday, so the 5 will be Sunday.

the problem is that I have several months, so when I change of month the formula (day) doesn't work, because EXCEL doesn't now that if you end the month of June in a Sunday the first day of July will be Monday.

It's that what I pretend

Tanhks.

[Bob Phillips]

Hi Norie,

what Ipretend is transform all the numbers in days of the week.

For example, the 4 of June is Saturday, so the 5 will be Sunday.

the problem is that I have several months, so when I change of month the formula (day) doesn't work, because EXCEL doesn't now that if you end the month of June in a Sunday the first day of July will be Monday.

It's that what I pretend

Tanhks.

How about

=TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),I5),"dddd")

[Ismael]

Hi,

=TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),I5),"dddd")

Doesn't solve my problem, I have the same result, let see if I can explain better.



In Excel whit the function DAY() the number 1 is Sunday.

Whit the formula that you send? me EXCEL return Friday.



But The result of the day (2005-06-01) must be Wednesday.



Ok I can solve this problem for the month of June, if I made =DAY(I5)+3

or

=TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),I5+5),"dddd")

But when I change of month for July the day one with this changing in the formula doesn't work.



What I ask is exist any possibility of EXCEL recognize when we change of month what is the day is written in cell before. For example if we are in cell I50 when this cell represent the first of JULY EXCEL will check in cell I49 what day is written, then is just put the next day.........



I don't now if this is easy to do or not.....



So if you guys can help me I appreciate.



regards,



Ismael

[Bob Phillips]

I think you fail to understand dates, and that is causing difficulty in understanding and solving your problem.

A value of 1 in a cell returns a day of Sunday, because Excel sees that as a date of 01-01-1900. If you want to get the day of a true date, you need have that true date defined somewhere.

For instance, put 01/01/2005 in A1, and copy that down. Then you can use

=TEXT(A1,"dddd")

in B1, and copy that down to get the day of any date.

Starting at a value of 1 is not going to help you as it masks everything.

[peacenik]

I think this is what is required

=WEEKDAY(B4)

Then format the cell as "dddd"

[Bob Phillips]

I think this is what is required

=WEEKDAY(B4)

Then format the cell as "dddd"

If only life were that simple.

[Ismael]

Hi,

Ok, my mistake l didn?t explain my doubt very well, but now the problem is solve.

Tanks for the help.

Best regards,

Ismael

[sheeeng]

I think this is what is required

=WEEKDAY(B4)

Then format the cell as "dddd"

I have forgotten this command ... Thanks for reminding

[peacenik]

I have forgotten this command ... Thanks for reminding

You're welcome.