date time format

  • Thread starter Thread starter Pam
  • Start date Start date
P

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
 
Hi... one way:

=H1-F1

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

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

It works.
 
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
 
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)
 
Back
Top