Time format
 

I tried a variation on this question earlier and recevied some useful
response but now that I understand my question more, maybe I acn narrow it
down.

I would like to store the time "15:00" in the cell but display "3".
Not 3pm or 3:00pm, just "3".

The "h" option in custom format gives me "15".

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200708/1

Time format
 

You have shown the number 3 in double quotes. Does that mean that you want it
to be text format or is that just the way you have described it?

If you want it as text then the following nested formula will convert the
hours of a time to text. It assumes that the time is in cell A1.

=LEFT(TEXT(A2,"h am/pm"),FIND(CHAR(32),TEXT(A2,"h am/pm"))-1)

However, if you want it to be a numeric value that you can perform
mathematical operations on then use the following:-

=VALUE(LEFT(TEXT(A1,"h am/pm"),FIND(CHAR(32),TEXT(A1,"h am/pm"))-1))

Regards,

OssieMac



"Emerogork via OfficeKB.com" wrote:

I tried a variation on this question earlier and recevied some useful
response but now that I understand my question more, maybe I acn narrow it
down.

I would like to store the time "15:00" in the cell but display "3".
Not 3pm or 3:00pm, just "3".

The "h" option in custom format gives me "15".

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200708/1

Time format
 

Format Cells Userdefined Format
in the Type box just type 3


"Emerogork via OfficeKB.com" skrev:

I tried a variation on this question earlier and recevied some useful
response but now that I understand my question more, maybe I acn narrow it
down.

I would like to store the time "15:00" in the cell but display "3".
Not 3pm or 3:00pm, just "3".

The "h" option in custom format gives me "15".

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200708/1

Time format
 

What do you mean by "Userdefined Format" in that context? I don't have
such an option uinder Format Cells in Excel 2003. If you type 3 into the
Type box in Custom format, it will display 3 for 15:00, but it will also
display 3 for 17:00, which probably isn't what the OP wants.
--
David Biddulph

"excelent" wrote in message
...
Format Cells Userdefined Format
in the Type box just type 3

"Emerogork via OfficeKB.com" skrev:

I tried a variation on this question earlier and recevied some useful
response but now that I understand my question more, maybe I acn narrow
it
down.

I would like to store the time "15:00" in the cell but display "3".
Not 3pm or 3:00pm, just "3".

The "h" option in custom format gives me "15".

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200708/1

Time format
 

This formula will do the trick

=(A1*24)-(A1--"12:00")*12


"Emerogork via OfficeKB.com" wrote:

I tried a variation on this question earlier and recevied some useful
response but now that I understand my question more, maybe I acn narrow it
down.

I would like to store the time "15:00" in the cell but display "3".
Not 3pm or 3:00pm, just "3".

The "h" option in custom format gives me "15".

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200708/1

Time format
 

I don't think the OP's question can be solved the way he wants, you gave him
one option by using a second cell that converts the value to an integer and
another would be am event macro that converts the value in the cell to an
integer directly but to keep the actual value of 15:00 in the cell but
displaying it as 3 is not possible


--

Regards,

Peo Sjoblom

"Teethless mama" wrote in message
...
This formula will do the trick

=(A1*24)-(A1--"12:00")*12


"Emerogork via OfficeKB.com" wrote:

I tried a variation on this question earlier and recevied some useful
response but now that I understand my question more, maybe I acn narrow
it
down.

I would like to store the time "15:00" in the cell but display "3".
Not 3pm or 3:00pm, just "3".

The "h" option in custom format gives me "15".

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200708/1