Cell format

[TIM WEIPERS]

Hi,

I need the cells in a workbook formatted for date time in order to be able to calculate time differences over midnight. However, I don't want the cell to actually show the date only the time. Is this possible ?

Many thanks
Tim

 

[Andrew Poulsom]

Just format the cells as hh:mm:ss.

 

[HalfAce]

Hello TIM WEIPERS,
Unless there's more to your question than I'm seeing, all you need to do is format
your range(s) to display the way you want.
If you enter '8/13/07 9:15:25 PM' into a cell and then format it as Time > 1:30 PM,
the cell's value will be the date/time but the display will be '9:15 PM'

Is that what you're looking for?

 

[TIM WEIPERS]

Hi Andrew / Halface,

I thought that might be the case. What I really want is for other users to be able to enter a time in cell B13 and another time in cell C14 (ie without them having to enter a date at all) and the difference to be shown in cell D13 even if the time in cell C14 has gone over midnight.

I don't think it'll be possible but who knows.
Thanks
Tim

 

[Andrew Poulsom]

If A1 contains the start time and A2 the end time:

=A2-A1+(A2<A1)

will cater for going over midnight.

 

[Norie]

If A1 contains the start time and A2 the end time:

=A2-A1+(A2<A1)

will cater for going over midnight.

 

[Andrew Poulsom]

Thankyou Norie. I edited my post.

 

[TIM WEIPERS]

Chaps,

Many thanks. With a bit of tinkering, it works (not tinkering with your formula; tinkering with the rest of mine !).

Cheers
Tim