date time format

[Pam]

Hope I explained this better:

Col E1(begdat) Col F1(begtime) Col G1(endd) Col H1 (endt)
02/01/04 1301 02/02/04 1420

The time in both f and h is formatted as custom 0000, when
I try to format this to time and choose: 13:01, it
changes everything to 00:00. I get nothing but zeroes
when there should be numbers there. I'm trying to do the
calculation that will tell me how long it took to book
someone from start to finish. Thanks

 

[Guest]

Hi... one way:

=H1-F1

Put that in, say, I1. Then put in another cell:

=LEFT(I1,2)&":"&RIGHT(I1,2)

It works.

 

[Frank Kabel]

Hi Pam
it would be better if you use a standard time format (currently you are
using numbers). But four your issue:
=H1-G1
formated also in '0000' will give you the difference.
to convert this to a time you may use
=TIME(LEFT(I1,2),RIGHT(I1,2),0)
and format this as time
Frank

 

[Peo Sjoblom]

If I2 holds for instance 730 (07:30) your formula will fail, you either need
to use an if function or something that will
convert it in another way

=TIME(INT(I2/100),MOD(I2,100),0)