Elapsed Time Format, [h]:mm, doesn't seem to work, have I forgotten something?

[awoohaw]

Hi Forum,
I was working on one of questions posted and either I have gotten my brain frozen or something has changed with excel.
Why is this format for elapsed time showing only 5 minutes instead of 24 hours and 5 minutes?

I am more than happy to admit I'm a goob if I'm doing something I should know better!

I have Version 2309 (Build 16827.20104. Current Channel (Preview)

 

[Fluff]

The time function returns time only, which will never be >= 24hours.

 

[awoohaw]

The time function returns time only, which will never be >= 24hours.

the excel time format has this option. Showing more than 24 hours is possible. But, the system generated sample above the choice list is showing the 5 minutes only as well.
I believe it should. And i was pretty sure it had in the past.

this excel support page also says it is possible:

Format numbers as dates or times - Microsoft Support

Date and time formats let you control the way dates and times appear in cells. For example, you can display 4/2/12 instead of 2-Apr or 7:00 instead of 7:00 AM.

support.microsoft.com

 

[Fluff]

That is the time format which is not the same as the function. If you use

=C1/1440

then the [h]:mm will work.

 

[awoohaw]

Thanks FLuff! I had no idea that the TIME(h,m,s) function did not work similarly to the Date(y,m,d) function by carrying over values.

Mr excel questions 63.xlsm
	B	C	D
1		1445	=24*60+5
2	0.003472222	00:05:00	=TIME(0,C1,0)
3	0.003472222	00:05:00	=TIME(24,5,0)
4	1.003472222	00:05:00	=1445/1440
5
6
7		45
8		2023-02-14	=DATE(2023,1,C7)
9		2023-01-14	=DATE(2023,0,C7)
Sheet3

Cell Formulas
Range	Formula
C1	C1	=24*60+5
D8:D9,D1:D4	D1	=FORMULATEXT(C1)
B2:B4	B2	=C2
C2	C2	=TIME(0,C1,0)
C3	C3	=TIME(24,5,0)
C4	C4	=1445/1440
C8	C8	=DATE(2023,1,C7)
C9	C9	=DATE(2023,0,C7)

 

[Fluff]

Glad to help & thanks for the feedback.