formatting

ads_1471

New Member
Joined
Apr 19, 2016
Messages
32
I have converted time format from 08:30 to be 830,

However when trying to change the number format from custom to general it goes to 0.3542

How do I stop it going to 0.3542 and stay as 830 in general format
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,966
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
You'd have to convert the actual cell value. 830 is not the same as 8:30 since Excel stores times as fractions of 1. Why do you need to do this?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,412
Office Version
365
Platform
Windows
It is important to understand Excel stores dates and times.
Basically, it stores them as numbers, specifically, the number of days since 1/0/1900. And time is just the fraction of a day. The date/time is just a special formatting on this number.
So, 8:30 AM is really 8.5/24, which is equal to 0.3542 (which is why you are seeing that).

If you want to convert it to a number like "830", you can use the following formula (for a cell on cell A1):
Code:
=TEXT(A1,[h]mm")*1
 

Watch MrExcel Video

Forum statistics

Threads
1,099,143
Messages
5,466,933
Members
406,511
Latest member
markflayd

This Week's Hot Topics

Top