TimeSheet date forumla questions

[lutherjt]

Thank you in advance to anyone willing to help a beginner out. I have read multiple posts but I'm still stumped. Here it goes:

I would like to have three active columns:
"Column B" a date in 1^st, 2^nd, 3^rd, etc. format. (Cells B10 through B25)
"Column C" a day of the week in Mon., Tues., Wed., format. (Cells C10 through C25)
"Cell L1" a bi-monthly date range

Basically I would like to select a bi-monthly date range in cell L1 and have columns B and C fill out with the correct date and day of the week accordingly. So if I input the date range of July 1^st-15^th, 2015 into Cell L1, then cells B10 through B24 would fill out the 1^st to the 15^th and then cells C10 through C24 would fill out Wed., Thurs., Fri., etc.

 

[Mackers]

It would be easier if you selected a start date and an end date in two separate cells, so in L1 you put 01/07/2015 and in M1 you put 15/07/2015 (formatted however you want - I'm from the UK so that format is the most natural for me). If you do this you don't have to use a formula to extract the date, and it's better practice to hold information like this.

On the basis that your start date is in L1, your end date is in M1 and your dates don't span more than one month (if they do I will need to amend the formula):

In B10, drag down however many days you want: =IF(DAY(L$1)+ROWS($1:1)-1>DAY(M$1), "", DAY(L$1)+ROWS($1:1)-1)

Or if you want it so say 1st, 2nd rather than 1, 2: =IF(DAY(L$1)+ROWS($1:1)-1>DAY(M$1), "", DAY(L$1)+ROWS($1:1)-1&LOOKUP(DAY(L$1)+ROWS($1:1)-1,{1,"st";2,"nd";3,"rd";4,"th";21,"st";22,"nd";23,"rd";24,"th";31,"st"}))

In C10, drag down: =IF(LEN(B10)>0, TEXT(L$1+ROWS($1:1)-1, "ddd"), "")

Hope that helps

Mackers

 

[lutherjt]

Worked perfectly, thank you so much!