How to get weekdays and dates from number?

[bhasoriya]

I have created small calendar, Column A has dates, Column B has days, Column C has Weeks


If i type 1 in cell E4, then F4 to F10 should haves dates from week 1 and G4 to G10 should have days(monday,tues,wed..)


Please help for these formulas.


For reference i have uploaded here file

https://rapidshare.com/files/439622681/calander.xlsx


Thanks

 

[DaveTurton]

I have put the formulas in your workbook, is this what you are after?


https://docs.google.com/file/d/0ByiZqUlQMjxhRHoyWVdhcGJRNEE/edit?usp=sharing

 

[NARAYANK991]

Hi Bhasoriya ,


One point before we get to the question you have posted ; your formulae in column B are using the DAY function ; this is wrong.


The DAY function merely gives the numeric value of the day of the month ; thus for a date such as 30th of any month , the DAY function will return the value 30 ; for the 17th of any month , the DAY function will return the value 17. Obviously this cannot be used to get the day of the week , for which you need to use the text function , using a format "dddd" ; thus :


=TEXT(DATEVALUE("03/03/2013"),"dddd")


will return the day of the week for March 3 , 2013 i.e. Sunday.


For your formulae , all you have to do is use the INDEX + MATCH combination to get the dates of the week ; if you change your formulae in column B , then all your weeks will start on Monday , and you don't need any formulae in column G , since the days will be fixed , from Monday through Sunday.


The formula in column F will be :


=INDEX($A$3:$A$366,MATCH($E$4,$C$3:$C$366,0))


Copy this downwards.


Narayan

 

[Kevin@Radstock]

Hi bhasoriya


Try the OFFSET function. Select cells F4:F10. Enter the formula:


=OFFSET(A1,MATCH(E4,C:C,0)-1,0,7,1)

This is a array formula, to commit; CTRL + SHIFT + ENTER

The in G4: =F4 copy down and format as "dddd".


Kevin

 

[bhasoriya]

Thanks to all for excellent response,

A specially narayank991, your knowledge is excellent.. Thank you very much..