format dates enigma

[Wanna Learn]

Hello I receive a monthly report that has dates that I need deciphered.
Column title is "week ending" the date is 08-10-26 , this means week
ending October 26 2008 . the next colum is "Day" and it has a 1 .(could be
any number from 1 to 5 . 1 being Monday, 2 = Tuesday 3 = Wednesday .
SO 08-10-26 1 = Mon 10/20/08 .
Is there a formula to do this. thanks in advance

 

[Joel]

You r ending data is always Sunday. therefore if you subtract 7 from the end
data and then add your day number 1 to 5 you will get the date you are
looking form

for October 26th

You start with October 19th and then add 1 to 5

so the formula is

=D5-7+E5 where D5 is October 26th , and E5 is the number 1 to 5

Make sure the cell where the formula is located is formated in any Date
format.

 

[Ron Rosenfeld]

Hello I receive a monthly report that has dates that I need deciphered.
Column title is "week ending" the date is 08-10-26 , this means week
ending October 26 2008 . the next colum is "Day" and it has a 1 .(could be
any number from 1 to 5 . 1 being Monday, 2 = Tuesday 3 = Wednesday .
SO 08-10-26 1 = Mon 10/20/08 .
Is there a formula to do this. thanks in advance

The formula will depend critically on the nature of the entry in the Week
Ending column.

If the entry is a true Excel date, that is formatted to appear as above
("yy-mm-dd"), then the formula would be:

=Week_Ending-WEEKDAY(Week_Ending-1-Day)

If the entry is a text string, and not a true date, then it needs to be
converted to a true date with this formula:

=DATE(LEFT(A2,2)+2000,MID(A2,4,2),RIGHT(A2,2))

Substituting:

=DATE(LEFT(A2,2)+2000,MID(A2,4,2),RIGHT(A2,2)) -
WEEKDAY(DATE(LEFT(A2,2)+2000,MID(A2,4,2),RIGHT(A2,2))
-1-Day
--ron

 

[Ron Rosenfeld]

=Week_Ending-WEEKDAY(Week_Ending-1-Day)

Joel's solution is simpler. But, depending on whether your week ending date is
a string or a true Excel date, you may still have to translate it as I
previously wrote.

But using Joel's simpler approach, that would be:

=DATE(LEFT(A2,2)+2000,MID(A2,4,2),RIGHT(A2,2)) - 7 + Days
--ron