Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Formula
In column B, I'm looking at data that has dates written in all types of
formats (data entry that was made by many different people). For example, one person might have written 07/09-08/09 for July 2009-August 2009, while another person wrote 07/ 09-08/ 09, or another wrote Jul 09-Aug 09. There are variety of dates, not just July 2009-August 2009. Is there a formula I could use that would take these different date combinations and translate months in column C and D and years in column E and F? For example, Column B: 08/09-10/09 Column C: August Column D: October Column E: 2009 Column F: 2009 If you could help, I'd really appreciate it! Thank you! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Formula
Column C:
=TEXT(DATEVALUE(LEFT(B2,FIND("-",B2)-1)),"mmmm") Column D: =TEXT(DATEVALUE(RIGHT(B2,LEN(B2)-FIND("-",B2))),"mmmm") Column E: =TEXT(DATEVALUE(LEFT(B2,FIND("-",B2)-1)),"yyyy") Column F: =TEXT(DATEVALUE(RIGHT(B2,LEN(B2)-FIND("-",B2))),"yyyy") Note that all of these return a text string. If you want numbers/values, you could remove the TEXT function from each formula, and simply custom format the cells as "mmmm" and "yyyy" (without quotes) respectively. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Liz J" wrote: In column B, I'm looking at data that has dates written in all types of formats (data entry that was made by many different people). For example, one person might have written 07/09-08/09 for July 2009-August 2009, while another person wrote 07/ 09-08/ 09, or another wrote Jul 09-Aug 09. There are variety of dates, not just July 2009-August 2009. Is there a formula I could use that would take these different date combinations and translate months in column C and D and years in column E and F? For example, Column B: 08/09-10/09 Column C: August Column D: October Column E: 2009 Column F: 2009 If you could help, I'd really appreciate it! Thank you! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula to look at current date and markdown date to give price | Excel Discussion (Misc queries) | |||
Create a formula in a date range to locate a specific date - ecel | Excel Discussion (Misc queries) | |||
Excel Formula to calulate number of days passed from date to date | Excel Discussion (Misc queries) | |||
Formula for determining if two date columns fall within specific date range | Excel Worksheet Functions | |||
Formula for determining if two date columns fall within specific date range | Excel Discussion (Misc queries) |