CODEWITHSUNDEEP
excelexcel-formula
Jaken
Jaken

Reputation: 1343

How to format a formula

In one column (E) I have values such as 2014-10-28 19:40:00+00:00. This is obviously date and time.

In my (F) Column I want to just use the date from that, so I've used the formula =LEFT(E:E,10) which worked nicely.

In my (G) Column I want the time, so I used =RIGHT(E:E,14) and it shows up with "19:40:00+00:00" which is what was expected.

Now I am trying to format the cells to have the time show up in a standard format of hh:mm. When I right click and select this format, nothing happens.

Is there a way to format the output of a formula or no?

Upvotes: 0

Views: 67

Answers (3)

SeanC
SeanC

Reputation: 15923

if your value in E is a date value, then =INT(E1) will extract the date, and =MOD(E1,1) will extract the time

If your value in E is text, then =INT(DATEVALUE(E1)) will extract the date, and =MOD(DATEVALUE(E1),1) will extract the time

Both will be in recognized formats for excel, and can then be formatted in any way you desire

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96771

If E1 contains:

2014-10-28 19:40:00+00:00

Then in G1 enter:

=MID(E1,12,5)

enter image description here

Upvotes: 1

CallumDA
CallumDA

Reputation: 12113

RIGHT and LEFT return strings, return them to their numeric values using the VALUE formula

=VALUE(LEFT(...))

Should allow you to use custom formats again

Upvotes: 1

Related Questions