Easy (I think) Excel question

[WallyWumpus]

Hi all,

I am trying to create a spreadsheet to help me with my roster each month.

I have a spreadsheet which has times in the following format: hh:mm Z eg: 20:30 Z

In order to be able to manipulate these as true times, I would love to be able to truncate the values to just hh:mm, and get rid of the Zulu letter.

Could anyone suggest a formula to do just that?

Thanks in advance

Wally.

[Whirlygig]

As long as you have the leading zero on the hh:mm format, this is will work

In the next column, put =left(A1,5) where cell A1 has the hh:mm Z. This will return hh:mm. Use copy/paste values to then convert this to a time format.

Cheers

Whirls

[Saab Dastard]

You could just do a Find & Replace of [space]Z with nothing.

SD

[WallyWumpus]

Both suggestions bloomin' marvellous, thank you!

[The Nr Fairy]

To make Whirlygig's suggestion more efficient, simply format the resulting cells as "date". You can then use them as time-formatted cells directly.

It does rely on every original cell having at least 6 characters, i.e. 12:34Z.