Easy (I think) Excel question
Thread Starter
Join Date: Oct 2007
Location: London
Posts: 118
Likes: 0
Received 0 Likes
on
0 Posts
Easy (I think) Excel question
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.
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.
Hovering AND talking
Join Date: Feb 2003
Location: Propping up bars in the Lands of D H Lawrence and Bishop Bonner
Age: 59
Posts: 5,705
Likes: 0
Received 0 Likes
on
0 Posts
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
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
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.
It does rely on every original cell having at least 6 characters, i.e. 12:34Z.