Changing time from 12 to 24 hours (not only the display)

[rprieto]

Hi everyone. I have a column with dates and times that for some reason has cells formatted either in 12 or 24 hour formats. I need every cell in 24 hour format; the problem is that altough I can format cells to change the display (custom: dd/mm/yyyy hh:mm:ss) the formatting on the cells is not changed (some are understood by excel as dd/mm/yyyy hh:mm:ss AM|PM and others as (...) dd/mm/ss 24 hour format). I need to use these dates in R (a statistical language) and since they are in different formats I get unpredictable interpretation problems.
Is there a way of interchanging the time formats (and not only the display in "format cells")? Example below:

<TABLE style="WIDTH: 95pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=127 border=0 x:str><COLGROUP><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4644" width=127><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 95pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=127 height=17>20/06/2008 19:24:56</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>20/06/2008 19:55:03</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>22/06/2008 14:09:57</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39635.631319444445">7/6/08 15:09</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39666.576458333337">8/6/08 13:50</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39666.795902777776">8/6/08 19:06</TD></TR></TBODY></TABLE>

 

[Gerald Higgins]

Hi, welcome to the board.
If your values are all numeric, then simply changing the format of all cells should be enough to change their appearance.
If you've tried this, and it's not working, I'm guessing that at least some of your date is not numeric values, it's text strings.
Can you confirm if this is the case ?
If yes, can you provide a couple of examples, and then there will be ways of converting the text string to a numeric time value.

 

[rprieto]

Hi Gerald,

yes, you are correct; while wayting for an answer from the forum I just noticed that some of the "dates" are actually not dates, only text. I've used "data\text to columns" to correct that, but then I get dates and times in different columns. Can I merge them?
thanks for the help,
rui

 

[rprieto]

I should have added that the cells containing the "dates" in the format dd/mm/yyyy hh:mm:ss above are the cells that are text strings, e.g.:

<TABLE style="WIDTH: 95pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=127 border=0 x:str><COLGROUP><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4644" width=127><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 95pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=127 height=17>24/06/2008 13:29:20</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>24/06/2008 20:47:50</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>24/06/2008 21:40:57</TD></TR></TBODY></TABLE>

 

[Gerald Higgins]

If they are really text strings, then something like this may work

=DATE((MID(a1,7,4)),(MID(a1,4,2)),(LEFT(a1,2)))+TIME(MID(a1,12,2),MID(a1,15,2),RIGHT(a1,2))

There may be better ways of doing this, however . . .

 

[rprieto]

That works! Thanks for enlighting me!

all the best,

rui