1

_________

I need to extract the date from C2 and find the difference between the date in c2 and A1

The date is formatted as "Jul, 18 2015", any ideas? The database has a different amount of text per cell. Is there away around this so that i can apply the formula to every cell and pull the day/month/year?

G198
  • 43
  • 6
  • Is the date always at the end of the string? Then you could get the last 13 characters for each cell and convert it to a date. If that's not the case then my next question would be if the date is always in parenthesis and there is no other text (ever) in parenthesis (other than dates). If that's the case then you could use `MID` together with `FIND` to extract the date. If that's not the case either then I am wondering how you want to extract the date at all. Also, will there be always only **one** date in column `C`? – Ralph Aug 24 '16 at 09:17
  • The date is always at the end of the string. =RIGHT(C2,13). that would work would'nt it? How would I convert the date since the date is stuck in between parenthesis ? – G198 Aug 24 '16 at 09:23
  • You could use `=SUBSTITUTE(RIGHT(C2,13),")","")` to extract the date and the function [DATEVALUE](https://support.office.com/en-us/article/DATEVALUE-function-df8b07d4-7761-4a93-bc33-b7471bbff252) to convert the string to an actual date. – Ralph Aug 24 '16 at 09:34
  • =DateValue, doesn't seem to work due "Jul , 06 2015". – G198 Aug 24 '16 at 09:42
  • Then you have two alternatives to convert the text to an actual date: (1) you write a custom UDF to convert the text to an actual date or (2) you write a real long Excel formula which converts `Jul` to `7`, `Dec` to `12`, `Jan` to `1`, and so on. Then you could use the `Date` function to convert the text to a date like this `=DATE(RIGHT(SUBSTITUTE(RIGHT(C2,13),")",""),4),7,MID(SUBSTITUTE(RIGHT(C2,13),")",""),6,2))`. Note, that this formula is using only the year and the day. The month is fixated to `7` for `Jul`. If you want to make it work for all month then the formula will get much longer. – Ralph Aug 24 '16 at 09:53
  • It is currently formatted as "Jul 16, 2015". Having trouble changing the "Jul" into a number? – G198 Aug 24 '16 at 10:43

1 Answers1

2

When the format of the date is always "Jul 18, 2015", then this formula should give you a datevalue. Just copy down.

=DATEVALUE(MID(RIGHT(C2,13),5,2)&LEFT(RIGHT(C2,13),3)&MID(RIGHT(C2,13),9,4))

This formula will just concatenate "18", "Jul" and "2015" to "18Jul2015", which excel will recognize as a date.

Marco Vos
  • 2,888
  • 1
  • 9
  • 10
  • "Error DATEVALUE parameter '' cannot be parsed to date/time." It is currently formatted as "Jul 16, 2015". – G198 Aug 24 '16 at 10:42
  • This is not the format in your original question. but it's an easy fix. See my edited answer. – Marco Vos Aug 24 '16 at 10:51
  • Yes sorry i was applying the example to my worksheet, using your edited formula, it has returned "42201" which isn't recognised as a date? The Format is still " Jul 16, 2015". – G198 Aug 24 '16 at 11:03
  • 42201 is a datenumber. Just format the cell you put the formula in as a date and 42201 will change to 7/16/2015. – Marco Vos Aug 24 '16 at 11:22
  • @G198: you might want to read the following solution I wrote some time ago in respect to dates. There you will find an explanation as to why 42201 **is** a date: http://stackoverflow.com/questions/37100821/change-date-format-using-substitute-or-replace/37101358#37101358 This might be also an interesting read: http://stackoverflow.com/questions/38000194/difference-between-date-and-time-w-out-work-week-excel/38001028#38001028 – Ralph Aug 24 '16 at 11:27