Time entry

[jonboy0766]

Hi

I want to display a time in HH:MM

I have Time(8,0,0) which returns 08:00

If i have TIME(72,0,0) it returns 00:00:00. I guess it is because I've gone over 24 hours i.e. one day. So how do I alter TIME(72,0,0) to return DD:hh:mm, can I have both formats in the same column?

Cheers

Jon

[Ace_XL]

With your current cell format use this forumla instead

=DATE(1900,1,INT(72/24))+TIME(MOD(72,24),0,0)

Change highlighted value as per requirement

[kvsrinivasamurthy]

Format the cell.
custom
[h]:mm

[kvsrinivasamurthy]

Format the cell.
custom
[h]:mm

[jonboy0766]

Thanks Ace

It still returns 00:00

my whole formula is

=IF(B3="Fault - P1",TIME(8,0,0),IF(B3="Fault - P2",TIME(23,59,59),IF(B3="Fault - P3",DATE(1900,1,INT(72/24))+TIME(MOD(72,24),0,0),"")))

so Fault - P1 returns 08:00
Fault - P2 returns 23:59

Fault - P3 returns 00:00 when I would like 72:00 (HH:MM) or 03:00:00 (DD:HH:MM) or whatever works.

Cheers

[Ace_XL]

Format cell as

dd:hh:mm
or
[hh]:mm