• Welcome to the Community Forums at HiveWire 3D! Please note that the user name you choose for our forum will be displayed to the public. Our store was closed as January 4, 2021. You can find HiveWire 3D and Lisa's Botanicals products, as well as many of our Contributing Artists, at Renderosity. This thread lists where many are now selling their products. Renderosity is generously putting products which were purchased at HiveWire 3D and are now sold at their store into customer accounts by gifting them. This is not an overnight process so please be patient, if you have already emailed them about this. If you have NOT emailed them, please see the 2nd post in this thread for instructions on what you need to do

Need help with Excel >__<

Ken1171

Esteemed
Contributing Artist
This may sound rather silly, but I don't seem to be able to make Excel 2013 calculate the difference between 2 hours of the day. Below is a simple example where cells A2 and B2 have different times, and cell C2 tries to subtract B2 from A2, and it gets into an error. The error message below it claims it's about wrong data types, where A2 and B2 were typed as "Time". Funny that it works if I subtract dates, but not times.

Any clue why this doesn't work?

Excel2013_HourDifferenceError.jpg
 

DanaTA

Distinguished
Firstly, what are you trying to accomplish? Do you need to determine how many hours and minutes have transpired between col. A and col. B? The formula itself looks right. Please check the formatting of both cells. It seems like one of them is a character format rather than time. For Col. C, format with the custom value "h:mm". This will give you hours rather than a time of day (no PM will appear)

For times that may span more than a 24 hour period, you'll need to change the format of the cells in cols. A and B to: m/d/yyyy h:mm AM/PM. Format the results cell as Number. This will give you number of hours (in whole hours and decimal fractions, not minutes).
 

Attachments

  • time formulas.png
    time formulas.png
    16.7 KB · Views: 149

Ken1171

Esteemed
Contributing Artist
Yes, the first 2 cells are formatted as "time", not text. I have tried all sorts of cell formats, but Excel 2013 still gives me that error. This only happens with TIME. If I enter dates instead, everything works. I have also tried the formulas you've shown in your example, but same error comes out. What version of Excel are you using? I ask because I have searched the web and none of what people have used seems to work in Excel 2013 - at least not for calculating TIME how many hours have passed. It seems like I can calculate anything else BUT time differences. It seems to be a case of exception.
 

Miss B

Drawing Life 1 Pixel at a Time
CV-BEE
OK, you had me scratching my head, as I use Excel for just about everything, and am always writing formulas, so couldn't understand why you were getting an error message.

Then it dawned on me, I have Excel 2010 on my laptop, so something might have been changed/updated in Excel 2013.

Other than that, did you also set cell C2 to be the same Time format? Below is a screenshot of what I got when I just tried it. The first row, however, showed AM instead of PM, probably because it's working on a 24 hour clock, so in the fourth row, I typed in the times in 24 hour format, but for some reason C4 is showing AM rather than PM, so not sure how to get that to display PM, unless I actually typed in the correct time, or IOW, if I typed 16:10 PM, which obviously isn't what you want.

Edited to Add: OK, I just tried deleting the PM from the A4 and B4 cells and it removed it from the C4 cell, and now it's showing the time, but not indicating whether it's AM or PM. Again, I'm not sure why you are having an issue with Excel 2013, as I'm not getting any error messages.

Excel2010Time.jpg


Edited to Add 2: OK, I think I figured out what to do as far as displaying the proper time, and not having the C4 cell showing AM instead of PM as the A4 and B4 are displaying.

Check out how I formatted the 3 cells, and it should work the way you want it to. If you still get the error message, then I have no idea what's causing it. You will still need to type the numbers in the A and B cells in 24 hour format, as I mentioned above.

Excel2010Time-2.jpg
 

Ken1171

Esteemed
Contributing Artist
You will still need to type the numbers in the A and B cells in 24 hour format

You have NAILED it, Miss B! That was it! The time cannot be typed in as AM/PM, which is how it automatically fills in the cells if you ask Excel for the current time. It MUST be typed in 24h format (no matter how the cell is formatted), or else I get the error. I would NEVER guess that on my own! Big thanks for the help! ^____^
 

Miss B

Drawing Life 1 Pixel at a Time
CV-BEE
You're welcome Ken. I've been doing spreadsheets for decades, first with Lotus 123, and then with Excel when the firm I worked for upgraded from DOS to Windows.
 

Ken1171

Esteemed
Contributing Artist
I have also figured out how to correct the error when the later date passes midnight, which makes the subtraction invalid. Now the formula works in any case. :)
 

DanaTA

Distinguished
I'm still on Office 2007. But the examples I researched, to be sure of what I was talking about, were on newer version. The page I viewed to confirm things was this one: Add or subtract time, which says it works in 2013 as well as newer versions. Perhaps you need to do a repair install. Just a guess.

Dana
 

DanaTA

Distinguished
Thought I posted that last one before, but I guess it didn't take...it was still in draft form at the bottom of the page! I must say, what I did worked just fine. And the fix for more than 24 hours worked as well.

Dana
 

Ken1171

Esteemed
Contributing Artist
I must say, what I did worked just fine.

The problem was neither with the formula, nor with the cell formatting. The problem was that, for this to work in Excel 2013, the times can only be typed in 24h format, or else the error comes out. Any attempts to type the time in AM/PM format gets into that error. Maybe that's unique to version 2013, I don't really know.

As it is, any attempts to enter times in AM/PM format results into a data type error. The cells are formatted to show the times in AM/PM format, I just can't type them in like that. I type them in 24h format, and they show in AM/PM format. Only then the data type errors stop. It's really odd.

Ohhhhh???? How did you fix that irregularity?

I just have to make a conditional check. If the later time is larger than the first, then just subtract as usual. Otherwise, add "1" to the first time, so it gets shifted to the same day as the later time, and then do the subtraction. See below for the formula, where "C3" is the first time, and "D3" is the later one.

=IF(D3>C3,D3-C3,(D3-C3+1))
 
Last edited:

Miss B

Drawing Life 1 Pixel at a Time
CV-BEE
I just have to make a conditional check. If the later time is larger than the first, then just subtract as usual. Otherwise, add "1" to the first time, so it gets shifted to the same day as the later time, and then do the subtraction. See below for the formula, where "C3" is the first time, and "D3" is the later one.

=IF(D3>C3,D3-C3,(D3-C3+1))
AHA! Good to know. I wasn't trying out times past midnight, so hadn't thought about that.
 

DanaTA

Distinguished
Ken, did you try the much simpler formula for the over 24 hour situation? That worked fine for me, with the full date format that you are using. Crap, I just realized that my screen capture of my spreadsheet example never made it up, either. This is very odd. Well, here it is:

Dana

Edit: This did post, further up the page. Oh, well.
 

Attachments

  • time formulas.png
    time formulas.png
    16.7 KB · Views: 134

Ken1171

Esteemed
Contributing Artist
Ken, did you try the much simpler formula for the over 24 hour situation?

I actually did, along with the many other suggestions I have found online. I was getting incorrect values from the formula you have suggested, like the values were really far off. For example, for a simple difference of 5h, I was getting values like 12h difference. I saw your formula suggested from other web sites, so I know it must work in some other context or perhaps Excel version, because it has worked for other people.

Under further research, Excel measures 24h of a day with a discrete value between zero and 1.0. For example, 12h would be 0.5 in Excel values. Therefore, when the 2nd time passes midnight, the first one will be off by the complement of 1.0, which results into errors in the difference calculations, To correct this, we just need to shift the first number by 1.0, so it now has the same zero point reference as the 2nd. This is why I add 1 to the first value if the 2nd is smaller, so both are in the same scale. Now the time difference calculations always give the correct values, assuming the 2nd time is never more than 24h apart form the 1st. If the 2nd time is more than a day apart, then the formula won't work anymore, but thankfully, that will never happen in my case, so this formula is fail-safe. :)
 

DanaTA

Distinguished
It's really odd that this wouldn't work for you, in a newer version. I wouldn't think they'd disable something like that. This would mean that millions of people would have to make changes to existing spreadsheets that perhaps are being used in a business environment. That means lots of lost time...and time is money in the business world. Perhaps it is a bug that was corrected in a newer version. They do that, sometimes...wait to fix a bug in a newer version, especially if that newer version is already well into development.

Dana
 

Ken1171

Esteemed
Contributing Artist
@DanaTA Yes, I also think this is odd, considering that time difference is a rather trivial operation. I have found other people having the issue on the web, so I was not alone on this. Looks like Microsoft already has a newer Office version out there, but I have no intention of upgrading because I don't use it often enough to justify it. As it stands, everything works with the spreadsheet, except that when I enter data into the table, I cannot type times as AM/PM because it breaks the formula. After I enter times in 24h format, the cells formatting converts them to AM/PM for displaying purposes without breaking anything.

When I use the Excel keyboard shortcut to enter the current time(CTRL+SHIFT+;), it does it in AM/PM format, and I have to retype it in 24h notation, or else the formula gives me that error again. It doesn't understand it's own automatic inputs. Someone suggested it could be a custom setting on Windows Regional settings, but I didn't see anything suspicions there.
 

DanaTA

Distinguished
I wouldn't know where to look for that. I guess I'd find it if I looked around, though. I don't like to mess with settings too much. Sometimes you cause more trouble for yourself when you do.

Dana
 

Ken1171

Esteemed
Contributing Artist
Hehe very true. I have been entering data into the table, and it's funny that when I add the current time (CTRL+SHIFT+; ), Excel uses AM/PM format, and this results into the wrong time difference instead of an error when used with the current formula. Funny that the cell formatting will display as AM/PM, but unless it's typed in as 24h format, the calculations will be off by a large margin. I have to be careful, because this is very confusing.
 

DanaTA

Distinguished
I prefer my database programming. The functions for working with time calculations are more straightforward. Sometimes it's better to just use a different tool. But that's only if that tool is available to you.

Dana
 

Ken1171

Esteemed
Contributing Artist
You mean SQL queries? That seems a bit overkill for something as simple as an hours sheet report.
 
Top