Timestamp to date - Excel 2010

[jubilee101]

Hi All, This is driving me nuts. I have a report which I have exported from a webpage into excel format. I want to equate a timestamp (10/15/2015 8:36:16 AM) to a Work Week number. Would usually do this by =WEEKNUM(A1,2). However getting the #VALUE error.

So figured the timestamp must be in text format. (My locale format setting are English - Ireland, date imported is in US format)

Have tried several things, here are a few:
=int(A1) Get #VALUE error
=DATEVALUE() - #Value error
=DATE(MID(S2,8,4),MONTH(DATEVALUE(LEFT(S2,3)&" 1")),MID(S2,5,2))

have also tried text to columns to strip out the date and chose M-D-Y as format. but no joy.

 

[DRSteele]

What happens when you wrap the timestamp figure in the function NUMBERVALUE? Try that and then create a test cell that refers to it and adds 1.

 

[teylyn]

Can you type here an example of what December 15 2015 should look in your system and what the date looks like that you import? It appears that Excel does not recognise the value as a date and delivers it as text. Hence any attempt to treat it as a date (with Int() or DateValue()) will fail. Your last formula might work if you extract the month with a MID() function as well instead of a DateValue().

cheers, teylyn