Formatting Time into decimal?

Speaking in exact mathematical terms, no. Decimal representation is 0 to .99, not 0 to chronological limits, otherwise its not based on the decimal numbering system.

You can however take the hours as hours and the minutes as minutes and represent them however you want. So if you have 6 hours and 45 minutes you can take those two numbers and display them any way you like, including as 6.45 which is not really the decimal representation (which would be 6.75). Displaying chronological time to appear like a decimal number without really being a decimal number will mean that we need to jump through some hoops/overcome some hurdles to get those results, as entering a value of 6.45 will be by default interpreted by Excel as a numeric decimal value representing 6 and 45/100, not 6 hours and 45/60 minutes.

What is the advantage for you showing these chronological values in a decimal like format instead of as a time format? IE why not just do 6:45 instead of 6.45, as many people will assume 6.45 is decimal format and follows decimal rules (IE: they should enter 6.75 for 6 hours and 45 minutes).

Hi, welcome to the forum

I echo the comments made above - excel is well capable of dealing with time, and has quite a few functions specifically designed to work with time. I always advice members that, if you are working with time, then stick with time in TIME format.

Another regular comment I make is that what you need to understand about dates and times in excel is…

a date is just a number representing the number of days passed since 1/1/900...and then formatted in a way that we recognize as a date. So, for instance, today (Fri 15 Jun 2018) is actually 43266

Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

Originally Posted by tragicus

The other issue I am having is it is sensitive about inputting time. If I accidentally typed a space or something after 8: when putting in 8 hours it zeros out or it wont calc with my sum if formula.

Excel is a relatively precise tool, your input should if nothing else be consistent. "8: 00" and "8:00" are not the same thing, regardless of if you do time format or not. Likewise "8.00" and "8. 00" are not the same thing either.

You can go over 24 hours using time by formatting as [h] or [hh], the square brackets tell Excel to allow hours beyond 24 hours.

Time and dates are hard to understand at first in Excel. As FDibbins points out dates are actually serial numbers from 1/1/1900 and time is decimal values from 0-1 representing a 24 hour cycle.

There are of course plenty of formulas for dealing with dates and times and there are mathematical ways to convert and manipulate times.

When working with dates and times its often prudent to use actual date/times for specific times of the day or dates and to use regular numbers converted to date/time values (later) when the number is meant to stand on its own. As an example:

If I wanted the number of hours and mins between 2 date/times I would have both date/times in date/time format and perform my calculations on them, then convert or represent the result however I want. IE:

(date/time) - (date/time) = decimal value
6/18/2-18 3:35 PM - 6/18/2018 9:35 AM = .25

I can convert the .25 to hours by simply formatting the cell as [h], which returns 6 (hours) difference. Truth is 6/18/2018 3:35 PM is really as a date value = 43269.6493055556, likewise the same date at 9:35 AM = 43269.3993055556. The whole time we are just dealing with numbers that Excel formats for us to look like dates and times. So if I format the cell as [h] it doesnt magically make my .25 = 6, it just displays it as 6.

As such, date and time are linked together. Even when you input a time in Excel formatted as time without you entering a date, one is assumed. So really, there is no such thing as time in Excel without dates. In other words there is no such thing as 6 hours. Its either a 6 that we as humans identify as 6 hours or its a decimal value representing 6 hours as part of a date/time value.

So you can convert 160 "hours" to a decimal value representative of 160 hours as a date/time value or you can convert time values into a whole number or decimal value representing the hours and then calculate results using 160 as a number representing 160 hours with those other values and after getting the result either convert it to a date/time equivalent or leave it as a number that people identify as representing hours.

So to my example, again the 6 formatted [h] is still actually .25, so if I wanted to subtract the 6 hours from my 160 hours, I cant just do the 160-cell (where cell has our .25 formatted [h]), as it would really do 160 (the number) - .25 (the decimal representing 6 hours in date/time context). What I would want to do is convert the .25 decimal representing date/time to a number representing the hours (6) by doing .25 * 24 = 6. We do this as .25 represents 1/4 of a full day which is 24 hours. Now we have an actual value of 6 (which we as people know is representing 6 hours) and can now subtract it from the 160 and get a result representing the number of hours left, even though to Excel it has no attachment to time, its just 154.