Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
format dates enigma
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
format dates enigma
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. "Wanna Learn" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
format dates enigma
On Thu, 6 Nov 2008 04:04:00 -0800, Wanna Learn
wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
format dates enigma
On Thu, 06 Nov 2008 07:30:54 -0500, Ron Rosenfeld
wrote: =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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CONVERT DATES FROM EUROPEAN FORMAT TO US FORMAT | New Users to Excel | |||
Format text 'dates' to real dates | Excel Worksheet Functions | |||
Format dates | Excel Discussion (Misc queries) | |||
keeping dates in UK format | Excel Discussion (Misc queries) | |||
the dates on cell format make different dates. | New Users to Excel |