Forum Discussion
PLEASE HELP ME. Excel Time Formatting Not Working?
PLEASE HELP ME!!! I created an excel workbook for tracking fitness workouts, I record the data such as weight lifted, number of reps, and rest times between sets. I formatted the cells so they express your rest times in terms of minutes and seconds (I used the custom "H:MM" format of time option). But whenever I record the amount of time you spend during cardio exercise, the formatting gets COMPLETELY SCREWED UP because when I type 28:44 (28 minutes and 44 seconds) of cardio duration, it instantly turns into 4:44 (4 minutes and 44 seconds). But if I type 20:00 (20 minutes) of cardio duration, it stays as 20:00 (20 minutes) and it never turns into something else. Why do some of the numbers like 28:44 transform into a complete different number when I enter them? I uploaded 3 screenshots below to better express what I mean.
'
In Excel 28:44 means 28 hours 44 minutes. If you want to enter 28 minutes 44 seconds, you have to enter like 00:28:44. If you want to enter 28 hour then format the cell as
[h]:mm
4 Replies
All previous comments have it dead on.
Can see you've a few alternative choices within custom that've been mentioned as well,
h:mm:ss, and mm:ss
The reason I commented though is that there is a Time category as well (instead of choosing Custom), where you'll likely find the breakdown you're looking for.
(You might find one breakdown works well for one workout category and another works better for another, so peruse the choices here as well).
On a side note, excellent demonstration of the issue at hand.
Work in ICT and are often tickets come in and users will show you everything but the cause of the issue.
I'd trade 1,000 names and numbers for a well placed screenshot. :Ddon't know why my previous post didn't show but what the others are trying to say is that you entered 28:44 but that is hours:min so that was 1day, 4hours, 44mins and showing hh:mm means you see 4:44 (the day is hidden).
you need to use the format mm:ss under custom to SHOW mins:sec
as noted by others, you need to ENTER the time 0:28:44 for excel to recognize it as hours:min:sec (if you only enter 28:44 excel assumes that to be hours:min)
Alternatively you can enter it as 28:44.0 and excel will recognize that as min:sec and tenths of a second
In Excel 28:44 means 28 hours 44 minutes. If you want to enter 28 minutes 44 seconds, you have to enter like 00:28:44. If you want to enter 28 hour then format the cell as
[h]:mmWhy choose h:mm when you want to key in minutes and seconds?
Set hh:mm:ss and key in 0:28:44 and 0:20:0.
