Forum Discussion

Copper Contributor

Jan 01, 2019

Solved

Time data issues

Hello all, As I mentioned in my first post, I am a complete novice with Excel, so I will probably be here often. I am having a terrible time working with elapsed time data. I’m trying to enter t...

Excel Upload.xlsx24 KB

office 365

Mourad-Louha
Jan 02, 2019
Hi Rick,

it looks like that some entries were e.g. entered as text and preserves that even after having changed the format to e.g. [h]:mm. Please e.g. double click the value and press Enter. Will then change the value to a time value (appears then as e.g. 15:53:00 in the formula bar).
Best,

Mourad

Mourad-Louha

MVP

Jan 01, 2019

Hi Rick,

with your choosen custom format hh:mm, the display does not take into account the number of elapsed days. Therefore it shows you 01:58 = 25:58-24:00 in the cell. The formula bar correctly shows 1 day (01.01.1900) + the time (01:58). You can enclose the hour code with squared brackets in your custom format for including the elapsed days into the display, e.g. [h]:mm.

Please have a look in this article for more possiblities regarding date and time formats in Excel.

Best,

Mourad

Rick.Lizotte

Copper Contributor

Jan 01, 2019

Thanks Mourad, that worked for that specific cell, but why do the cell entries in both rows 7 and 11 show a combination of text entries and time entries when both rows are formatted as time? I don't understand that at all.

Thanks again for your help,

Rick Lizotte

Mourad-Louha
MVP
Jan 02, 2019
Hi Rick,

it looks like that some entries were e.g. entered as text and preserves that even after having changed the format to e.g. [h]:mm. Please e.g. double click the value and press Enter. Will then change the value to a time value (appears then as e.g. 15:53:00 in the formula bar).
Best,

Mourad
- Rick.Lizotte
  Copper Contributor
  Jan 02, 2019
  Thanks Mourad, that did the trick. I had no idea the formatting didn't apply immediately.
  
  You've been a big help. Thanks again.
  
  Rick Lizotte