Excel hh:mm format

[The Mysterious J]

In some worksheets, a cell formatted hh:mm with a value of 24 will yield the
desired result - 24:00 (24 hours). As of today some worksheets are yielding
00:00 for the same value of 24, and in the formula bar, you can see that the
24 has been converted to 1/24/1900 12:00:00AM - the 24th day of the century.
How can this be corrected so that it will show 24 hours.

 

[Fred Smith]

To get 24:00 displayed, you need a format of [hh]:mm, not hh:mm.

However, I doubt that's your problem. To get the results you are showing,
someone must have entered 24, as opposed to 24:00, which Excel interpreted
as 24 days, rather than 24 hours. Correct the data entry, and you should be
fine.

Regards,
Fred.

 

[The Mysterious J]

This still didn't quite work. Using format [hh]:mm and entering the value 24
still converted the 24 into 01/24/1900 12:00 a.m., only now the display shows
576:00 - because 24 days into the century meant 576 hours had passed. Is
there something else I should be changing to get the value 24 to show as 24
hours, and not 24 days?

To get 24:00 displayed, you need a format of [hh]:mm, not hh:mm.

However, I doubt that's your problem. To get the results you are showing,
someone must have entered 24, as opposed to 24:00, which Excel interpreted
as 24 days, rather than 24 hours. Correct the data entry, and you should be
fine.

Regards,
Fred.

In some worksheets, a cell formatted hh:mm with a value of 24 will yield
the
desired result - 24:00 (24 hours). As of today some worksheets are
yielding
00:00 for the same value of 24, and in the formula bar, you can see that
the
24 has been converted to 1/24/1900 12:00:00AM - the 24th day of the
century.
How can this be corrected so that it will show 24 hours.

 

[pdberger]

J --

I think the previous answer may have addressed the problem -- if you're
entering '24', that's the issue. You should be entering '24:00'. Does that
fix it?

HTH

This still didn't quite work. Using format [hh]:mm and entering the value 24
still converted the 24 into 01/24/1900 12:00 a.m., only now the display shows
576:00 - because 24 days into the century meant 576 hours had passed. Is
there something else I should be changing to get the value 24 to show as 24
hours, and not 24 days?

To get 24:00 displayed, you need a format of [hh]:mm, not hh:mm.

However, I doubt that's your problem. To get the results you are showing,
someone must have entered 24, as opposed to 24:00, which Excel interpreted
as 24 days, rather than 24 hours. Correct the data entry, and you should be
fine.

Regards,
Fred.

In some worksheets, a cell formatted hh:mm with a value of 24 will yield
the
desired result - 24:00 (24 hours). As of today some worksheets are
yielding
00:00 for the same value of 24, and in the formula bar, you can see that
the
24 has been converted to 1/24/1900 12:00:00AM - the 24th day of the
century.
How can this be corrected so that it will show 24 hours.

 

[Fred Smith]

No. As you found out, Excel interprets 24 as 24 days. For Excel to recognize
an entry as a time, you must include a colon. Your choices are:

1. Enter the colon.
2. Divide the entry by 24 (the number of hours in a day)
3. Write a macro which converts 24 days into 24 hours.

Regards,
Fred.

This still didn't quite work. Using format [hh]:mm and entering the value
24
still converted the 24 into 01/24/1900 12:00 a.m., only now the display
shows
576:00 - because 24 days into the century meant 576 hours had passed. Is
there something else I should be changing to get the value 24 to show as
24
hours, and not 24 days?

To get 24:00 displayed, you need a format of [hh]:mm, not hh:mm.

However, I doubt that's your problem. To get the results you are showing,
someone must have entered 24, as opposed to 24:00, which Excel
interpreted
as 24 days, rather than 24 hours. Correct the data entry, and you should
be
fine.

Regards,
Fred.

In some worksheets, a cell formatted hh:mm with a value of 24 will
yield
the
desired result - 24:00 (24 hours). As of today some worksheets are
yielding
00:00 for the same value of 24, and in the formula bar, you can see
that
the
24 has been converted to 1/24/1900 12:00:00AM - the 24th day of the
century.
How can this be corrected so that it will show 24 hours.

 

[The Mysterious J]

Perfect! Thank you. It also makes sense why we just ran into this problem now
- most of our services are in minutes (0:60, 0:15, 0:45, 1:30, et cetera).
This only came up because it was for inpatient services, which are 24-hour
services. I'll pass it on to my team.

No. As you found out, Excel interprets 24 as 24 days. For Excel to recognize
an entry as a time, you must include a colon. Your choices are:

1. Enter the colon.
2. Divide the entry by 24 (the number of hours in a day)
3. Write a macro which converts 24 days into 24 hours.

Regards,
Fred.

This still didn't quite work. Using format [hh]:mm and entering the value
24
still converted the 24 into 01/24/1900 12:00 a.m., only now the display
shows
576:00 - because 24 days into the century meant 576 hours had passed. Is
there something else I should be changing to get the value 24 to show as
24
hours, and not 24 days?

To get 24:00 displayed, you need a format of [hh]:mm, not hh:mm.

However, I doubt that's your problem. To get the results you are showing,
someone must have entered 24, as opposed to 24:00, which Excel
interpreted
as 24 days, rather than 24 hours. Correct the data entry, and you should
be
fine.

Regards,
Fred.

message In some worksheets, a cell formatted hh:mm with a value of 24 will
yield
the
desired result - 24:00 (24 hours). As of today some worksheets are
yielding
00:00 for the same value of 24, and in the formula bar, you can see
that
the
24 has been converted to 1/24/1900 12:00:00AM - the 24th day of the
century.
How can this be corrected so that it will show 24 hours.

 

[Julia Sheppard]

Hello, I've just read your post wanting to know how to insert various 24hr
times in hours & minutes.
I need to record in excel a start time a finish time & the difference ( hrs
worked). I see your problem was solved but for the life of me I don't
understand the solution. I'd be ever so grateful if you help me or even
better send me a copy of a basic spreadsheet.
My email address is: (e-mail address removed)
Thanks ever so much in advance for any help you can give me.

Perfect! Thank you. It also makes sense why we just ran into this problem now
- most of our services are in minutes (0:60, 0:15, 0:45, 1:30, et cetera).
This only came up because it was for inpatient services, which are 24-hour
services. I'll pass it on to my team.

No. As you found out, Excel interprets 24 as 24 days. For Excel to recognize
an entry as a time, you must include a colon. Your choices are:

1. Enter the colon.
2. Divide the entry by 24 (the number of hours in a day)
3. Write a macro which converts 24 days into 24 hours.

Regards,
Fred.

This still didn't quite work. Using format [hh]:mm and entering the value
24
still converted the 24 into 01/24/1900 12:00 a.m., only now the display
shows
576:00 - because 24 days into the century meant 576 hours had passed. Is
there something else I should be changing to get the value 24 to show as
24
hours, and not 24 days?

:

To get 24:00 displayed, you need a format of [hh]:mm, not hh:mm.

However, I doubt that's your problem. To get the results you are showing,
someone must have entered 24, as opposed to 24:00, which Excel
interpreted
as 24 days, rather than 24 hours. Correct the data entry, and you should
be
fine.

Regards,
Fred.

message In some worksheets, a cell formatted hh:mm with a value of 24 will
yield
the
desired result - 24:00 (24 hours). As of today some worksheets are
yielding
00:00 for the same value of 24, and in the formula bar, you can see
that
the
24 has been converted to 1/24/1900 12:00:00AM - the 24th day of the
century.
How can this be corrected so that it will show 24 hours.