Extracting Time from American Date+Time format

**Perkins20** · 07-09-2012, 05:51 AM

Hi
I want to extract time from an American date and time series that I have dumped into excel. I want to use =mod(A1,1), or similar. The problem is, that because I have my excel set to English UK, and this is in American format, every time I get a date such as 04/13/2009 10:30 I get a #value for =mod(A1,1), as I assume that it is interpreting this as '13th month' and is returning an error (when in actual fact the date is 13th of April).

How can I extract the time regardless of date format?

thanks very much in advance.

**npamcpp** · 07-09-2012, 06:06 AM

Hi,

if the date is a real date, then the format US/UK does not matter. Format is just cosmetics. If the underlying data value is a number (and every date/time value is a number) then you should be able to run the MOD() function to extract the time.

Is your date value a real date? Do the test: Format the cell with the "General" format. Does it show as a number similar to this:

41099.9201388889

If not, it is not a real date, but most likely text that looks like a date/time value.

Post a data sample for inspection (in an Excel file, of course)

**Ace_XL** · 07-09-2012, 06:09 AM

use

=iferror(TIME(HOUR(A1),MINUTE(A1),SECOND(A1)),RIGHT(A1,LEN(A1)-FIND(" ",A1)*1))

then format cell to "h:mm"

**martindwilson** · 07-09-2012, 06:14 AM

=RIGHT((TEXT(A1,"mm/dd/yyyy hh:mm")),5)+0

**Perkins20** · 07-09-2012, 06:32 AM

Thanks very much for your responses. I did the 'text or date' test, and when I changed to general format it still looked like 07/13/2009 10:30 so I assume it is just text? I have now used =right(A1,5) which seems to work fine.