Convert Text Dates & Time to Values?

Hi,

I have the following:

Entry Date To Be Billed Hrs
18 Sep 2012 08:00
Cells: E5 F5

They were exported out of a system & now are text. I'm looking to compile the data, by weeks & a basic sum function is not recognizing, which I'm assuming is because these are text files.


How can put a function or two off into another column to get numerical values in which I can sum up?

Thanks

its hard to see from your post what part is in which cell?

Entry Date To Be Billed Hrs
18 Sep 2012 08:00
Cells: E5 F5

Entry Date To Be ...Billed Hrs
18 Sep 2012... 08:00
Cells: E5... F5
like that?

Hi

In Cell E5, has: 18 Sep 2012
Cell F5, has: 08:00

Thanks

Hi MyCon,

Try multiplying these Text date and Hrs with 1. Type 1 some where copy that, now select data and time columns, paste special -> values and multiply -> enter.

Regards,
DILIPandey
<click on below * if this helps>

try...
=datevalue(E5)
=timevalue(F5)

Hi FDbbins,

Your functions works great!

Hi dilipandey,

Your suggestion also works.

Now, some of my hour totals have triple digits, such as: 300:00 hours.

I thought I could simply change the time format to use something like: [h]:mm, but this doesn't seem to work. How to get this corrected?

Thanks again everyone for your assistance.

you also try the text-to-column-> delimeted -> there are options there for General ,date ,etc.

Hi MyCon,

I thought I could simply change the time format to use something like: [h]:mm

It may be happening because of [h]:mm only.. try change time format to hh:mm if you are sure that value is not text

Regards,
DILIPandey
<click on below * if this helps>

Hi,

Tried, hh:mm & [hh]:mm & neither works.

I have some instances, where there's 1000+ hours & this total needs to be shown.

Is there another suggested format option?

Thanks

It should work

It's hard to suggest something without seeing the data in excel otherwise the conversation will get lengthier by thinking about the possibilities.

Please add the sample data in excel and your expected output and attach it here for better understanding.