Reading ages problem in Excel

[cosmica67]

Hi,

I have a bit of a problem that I hope someone can solve for me.

I have reading ages of pupils in excel shown as YY:MM, for example 10:05 which equates to 10 years and 05 months.

I have to import this data into a MIS which requires the data to be in the format 10/05.

However, when I try to format the excel csv file to show 10/05, I get an integer or a date format which the MIS cannot handle and throws back an error message.

Is there a way of converting 10:05 into 10/05 easily in Excel. When I format a different cell to 'text' it works but I have to manually change each reading age and I have hundreds to do and this defeats the object.

Many thanks

Regards

[Glenn Kennedy]

Will you please attach a sample Excel workbook? Please don't attach a picture of one.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[cosmica67]

Hi,

Please find attached excel spreadsheet.

I have included another excel spreadsheet with the same data and the desired solution

Regards

[cosmica67]

Even after saving the csv file with the 10/05 etc format, it is converted into a date format when saved.

[AlanY]

try this

=TEXT(HOUR(A2),"00")&"/"&TEXT(MINUTE(A2),"00")

[AliGW]

You said the format of the cells was yy:mm, which i thought odd, but it isn't - it's hh:mm.

Try this in E2, dragged down:

=HOUR(A2)&"/"&MINUTE(A2)

[Glenn Kennedy]

=INT(A2*24)&"/"&TEXT(60*MOD(A2*24,1),"00")

but others have got there, too....

[cosmica67]

worked perfectly!

you've solved the problem that has baffled me and others for 3 days!

Thank you so much!!

[cosmica67]

Thank you so much!!

Your initial reply to my post has resulted in this thread being resolved.

[AliGW]

Glad to have helped!