I need to put 10 AM to 13:25 PM. I know that this is 3.25 hours, however, my formula puts it as 3.4. How do I show it as 3.25 hours? I also have to put 8:00 AM to 15:45 PM and it shows 7.8 instead of 7.75 hours. Thanks!
Excel is "storing" the correct result - e.g. 7.75 hours, However, to get Excel to display 7.75 hours rather than 7.8, you simply change the way that this cell is "formatted." In this case, if you add another decimal point to the formatting, you will see 7.75.
Easiest way to bring up the "Format Cells" Dialog box is to use the Ctrl + 1 (one) keyboard shortcut. In Number format, change to 2 decimal places.
Ok...that worked on the 7.75 but when I put in 10:00 AM to 16:25 PM it comes up 6.42 in that same cell instead of 6.25 hours. Is there a way to make it work to where it shows .25, .5, .75 regardless of what time in enter?
I'm sorry to keep bothering you but you have been a great deal of help from watching your videos. I have completely wiped out all formulas and put all formulas back in and I still cannot get it to show 6.25. It keeps coming up as 6.42. I am not us AM/PM in the formatting. Is there something else that I may be doing wrong? All other decomals work( .5 and .75) but for some reason .25 will not work.
I tried Clear All and that did not work. I'm still getting 6.42 and not 6.25. I have used Excel 2003 as well as 2007 and I still get the same answer. I'm kinda stumped on this one.
Well - I don't know what else to suggest - other than opening up a brand new Excel Workbook and starting fresh.
When I entered in the time values that you gave me, I got the correct answer. I could not find a way to replicate the incorrect results that you report.
Yes, the difference between 10:00 AM and 1:30 PM is 3:30 - 3 hours and 30 minutes (presuming that the formula cell is "formatted" as h:mm. In order to have the formula result show as 3.5, do this: =(later time - earlier time)*24 and then format the resulting formula cell as a "number" with 1 decimal place.
Be sure to use the parenthesis () for the subtraction & then multiply* that result by 24.(hours)
Well, how does one format simply input a time? For example, I format for hours and minutes. I input 852 and expect 8:52 but Excel 2007 returns 00:00 Very frustrating.
Excel is "very picky" when it comes to how you input TIMES and DATES. For example, if you input 8:52, by default, Excel will intertpet that entry as 8:52 AM. So, if you want to show this as Hours and Minutes only, FORMAT the cell as CUSTOM h:mm
If, you want to show 8:52 as 8:52 PM (20:52 in Military Time) enter is as 8:52 SPACE "p" - Excel will then automatically convert this to 8:52 PM and it will align the time to the RIGHT SIDE of the Cell.
@DannyRocksExcels , thanks for taking the time to respond. Will Excel allow time input without inserting the : between the hour & minutes, or does this require some kind of vba script?
Excel insists that there be a colon (:) to separate hour:Minute:Second (Second is optional). 11:00 is considered 11:00 AM (Military Time). For PM, type 11:00(Single Space)p and hit Enter. Excel will automaticall enter this as 11:00 PM (Capitalized PM) - That is an easy way to ensure that you entered the time correctly. Also, look for Time to be Right-Aligned in the Cell - that confirms that the TIME is correctly stored as a NUMBER (So that you can perform Time Calculations.)
Hi Danny!
I need to put 10 AM to 13:25 PM. I know that this is 3.25 hours, however, my formula puts it as 3.4. How do I show it as 3.25 hours? I also have to put 8:00 AM to 15:45 PM and it shows 7.8 instead of 7.75 hours. Thanks!
11keifferr 7 months ago
@11keifferr
Excel is "storing" the correct result - e.g. 7.75 hours, However, to get Excel to display 7.75 hours rather than 7.8, you simply change the way that this cell is "formatted." In this case, if you add another decimal point to the formatting, you will see 7.75.
Easiest way to bring up the "Format Cells" Dialog box is to use the Ctrl + 1 (one) keyboard shortcut. In Number format, change to 2 decimal places.
Danny Rocks
The Company Rocks
DannyRocksExcels 7 months ago
@DannyRocksExcels
Ok...that worked on the 7.75 but when I put in 10:00 AM to 16:25 PM it comes up 6.42 in that same cell instead of 6.25 hours. Is there a way to make it work to where it shows .25, .5, .75 regardless of what time in enter?
11keifferr 7 months ago
@11keifferr
I just entered this calculation in Excel and got 6.25 hours as the result. Here is my suggestion:
1) Do NOT mix AM/PM formatting with Military (24 hour) Time.
2) So, make the starting time - 10:00 and the ending time 16:25 and you should get the correct result - 6.25 hours.
Danny Rocks
The Company Rocks
DannyRocksExcels 7 months ago
@DannyRocksExcels
I'm sorry to keep bothering you but you have been a great deal of help from watching your videos. I have completely wiped out all formulas and put all formulas back in and I still cannot get it to show 6.25. It keeps coming up as 6.42. I am not us AM/PM in the formatting. Is there something else that I may be doing wrong? All other decomals work( .5 and .75) but for some reason .25 will not work.
11keifferr 7 months ago
@11keifferr
Not a problem - I think that I now know the "core problem" here:
1) When you delete a single cell or a group of cells, you ONLY delete the "contents of those cells" - text, numbers, formulas, etc.
2) Deleting a cell does NOT remove the "underlying" FORMATTING for those cells! And, that is where the problem lies in my experience.
If you are using Excel 2003 or older, select the cells and then go to the Edit Menu and select Clear - Clear All.
DannyRocksExcels 7 months ago
@DannyRocksExcels
I tried Clear All and that did not work. I'm still getting 6.42 and not 6.25. I have used Excel 2003 as well as 2007 and I still get the same answer. I'm kinda stumped on this one.
11keifferr 7 months ago
@11keifferr
Well - I don't know what else to suggest - other than opening up a brand new Excel Workbook and starting fresh.
When I entered in the time values that you gave me, I got the correct answer. I could not find a way to replicate the incorrect results that you report.
Sorry!
Danny Rocks
The Company Rocks
DannyRocksExcels 7 months ago
Hi. I need to put 10 AM to 1:30 PM. I know that this is 3.5 hours, however, my formula puts it as 3:30. Thank you!
guitarsforever 1 year ago
@guitarsforever
Yes, the difference between 10:00 AM and 1:30 PM is 3:30 - 3 hours and 30 minutes (presuming that the formula cell is "formatted" as h:mm. In order to have the formula result show as 3.5, do this: =(later time - earlier time)*24 and then format the resulting formula cell as a "number" with 1 decimal place.
Be sure to use the parenthesis () for the subtraction & then multiply* that result by 24.(hours)
That will get you the result you are looking for.
Danny Rocks
DannyRocksExcels 1 year ago
Well, how does one format simply input a time? For example, I format for hours and minutes. I input 852 and expect 8:52 but Excel 2007 returns 00:00 Very frustrating.
morfeeene 1 year ago
@morfeeene
Excel is "very picky" when it comes to how you input TIMES and DATES. For example, if you input 8:52, by default, Excel will intertpet that entry as 8:52 AM. So, if you want to show this as Hours and Minutes only, FORMAT the cell as CUSTOM h:mm
If, you want to show 8:52 as 8:52 PM (20:52 in Military Time) enter is as 8:52 SPACE "p" - Excel will then automatically convert this to 8:52 PM and it will align the time to the RIGHT SIDE of the Cell.
Danny
DannyRocksExcels 1 year ago
@DannyRocksExcels , thanks for taking the time to respond. Will Excel allow time input without inserting the : between the hour & minutes, or does this require some kind of vba script?
morfeeene 1 year ago
@morfeeene
Excel insists that there be a colon (:) to separate hour:Minute:Second (Second is optional). 11:00 is considered 11:00 AM (Military Time). For PM, type 11:00(Single Space)p and hit Enter. Excel will automaticall enter this as 11:00 PM (Capitalized PM) - That is an easy way to ensure that you entered the time correctly. Also, look for Time to be Right-Aligned in the Cell - that confirms that the TIME is correctly stored as a NUMBER (So that you can perform Time Calculations.)
DannyRocksExcels 1 year ago
Awesome tutorial DannyRocksExcels !!
Great & useful Video tutorial
Thanks so much Danny
Keep upload more tutorial videos
Your tutorial are so valuable for me & all Youtubers
Thanks a Lot
God Bless you :-)
tirathmistry 2 years ago