Support Solved Small Problems with Automatic Formula Conversion to Excel

As I can get Office 365 free since I'm a student at my college, I went ahead and got it and it converted formulas where necessary. However, I'm running into the problems with my IF conditional for my activity log when the time is PM, which you helped me with previously, @Mixt, here. I was using commas in my formula (Microsoft Excel) instead of semi-colons (OpenOffice Calc) because whoever was helping me over on The Admin Zone thought I was using Microsoft Excel, which is what gave me my problems. However, with the automatic conversion, it seems to have converted properly, such as from semi-colons to commas, but there is an issue as mentioned.

This is on OpenOffice Calc:


And this is what I'm getting on Microsoft Excel:


I think it has something to do with the negatives (PMs) based on the message, so how can I fix that? (It's not the column widths as I double click there so they're just right, and I tried expanding them just to see, anyway, to no avail.) If there are enough negatives (PMs), even the average and rounded columns are screwed up. So it might not be the formula, but I'm not sure.

 

Fair warning, I don't have Excel here so I can't test formulas before I send them to you. So make especially sure to test that they work as you want them to.

Anyway, it indeed looks like Excel just isn't equipped to handle negative values of time. So instead of moving the PMs back we need to move the AMs forward. So something like this should work.

=IF(HOUR(C2)<12,C2+1,C2)

 

So it's exactly the same thing, just the other way around. :)



So just as a refresher, what was the previous formula saying and what is the current one saying?

Edit: If we wanted to keep it the other way, I believe I found some useful links:

• http://excelribbon.tips.net/T006239_Displaying_Negative_Times.html
• https://support.microsoft.com/en-us...e-value-are-displayed-as-pound-signs-in-excel

Edit: There isn't a way to do a mass find and replace of this, is there, or do I have to do it all manually?

 

Basically if you had logged a time like 1AM the math was treating that as early in the morning instead of late at night, pulling the average way down. In hindsight it might have been better to set the break point at like 6AM instead of noon. But so far you haven't had issues of times registering as later that you wanted, so no harm in keeping it.

There is a find and replace option. I'm not sure where they put it in menus these days, but a quick google says that the hotkey of Ctrl+H should work.

 

@Mixt, I'm still using OpenOffice Calc for only this document because I haven't really gotten around to it and it's much larger than my other spreadsheets.

Anyway, you provided me an excellent workaround to move the AM times forward instead; however, in your opinion, what do you think is better? Moving the AM times forward or keeping it with the PM times moved backward and using the following solution to deal with the issue of negative time values?

https://support.microsoft.com/en-us...e-value-are-displayed-as-pound-signs-in-excel

What does changing the date system do, exactly, and does it potentially cause any other issues or do you see it as a good solution? (The solution was provided by Microsoft themselves.)

The reason I ask which is better, in your opinion, is because when we move the AM times forward, there's really no indication under the AM/PM column as seen in the third post's screenshot. However, when we move the PM times backward, there is an indication: the negative sign as well as how many minutes behind 12:00 AM the time is. Even though the calculations are the same regardless—for example, the average will be 10:53 PM either way—I guess it's just good to know exactly what you're finding the average of when it's between two different days as when it's all either AM or PM on the same day or you do want to find the averages between AM and PM on the same day, you can just use the plain ol' average formula, but since we're finding the averages between the PM times of the previous day and the AM times of the current day, we need an IF conditional. The latest—or earliest, if you'd rather—time I currently have logged for going to bed around is 5:22 AM, and I can't ever, for example, foresee myself going to bed for Saturday at 12:30 PM on Sunday, which would make finding the average even more complicated. I don't think anything will ever go past 5:30 AM, to be honest, as even that 5:22 AM was the only time logged for the 5:00 AM - 5:59 AM range so far since I've started this project. The more common times are between the 11:00 PM and 1:59 AM range.

Ultimately, the decision is up to me, but it never hurts to solicit others' opinions, right? :)

 

Update! Doesn't seem like that solution works that well:



Future dates which can be easily fixed, but too time consuming as you can't get specific enough with the find and replace feature there; negative average, causing an error with the rounding formula.

Edit: I wonder if it's possible to use conditional formatting with the formula to highlight the numbers where we add one or vice-versa where we don't add one. That would care care of there being an indication.

 

So that date system apparently handles negative time such that negative time is mirrored rather than just more days.

So when you get the result of -12:17 AM, what that is really saying is "17 minutes into negative time" where what we want is more like "17 minutes before the end of the day" or 11:43 PM. It did the math right, it's just presenting the result in a way that isn't very intuitive.

If you want the date system that way you could do something like adding 1 to average to force it into positive time. But you'll have to edit formulas either way.

I forgot that formulas don't work well in find and replace though. It's the kind of problem I might be able to muddle through myself, but I don't think I can walk someone else through it. Not without it probably taking longer than just hand replacing them.

 

@Mixt, I've fully converted my log over to Excel now. I just ended up going with the original solution you gave me of changing =IF(HOUR(C2)>12,C2-1,C2) to =IF(HOUR(C2)<12,C2+1,C2), so instead of subtracting one hour when the time is any time that is 11:59 PM or below—specifically, between 12:00 PM and 11:59 PM—we add one hour when the the time is any time that is 12:00 AM or above—specifically, between 12:00 AM and 11:59 AM. (Do I have that down right?)

It works exactly the same way, there's just no indication now of what's what as mentioned. So can I use conditional formatting to highlight cells that are having one hour added to them (AM) or, vice-versa, highlight cells that are having nothing being done to them (PM)?

 

You are adding one day, not one hour, but other than that you're good.

If you want to use conditional formatting to see what changed in column D the expected value is less than one if unchanged, and greater than or equal to one if changed.

 

One final thing, @Mixt: Today while working on an assignment in IT 111, which focuses specifically on spreadsheets, I discovered you can combine formulas, such as averaging and rounding in one cell without having to do it in two separate cells, typically making it neater. Like, in my activity log, I have two columns basically giving the same information, just that one is rounding. Anyway, so for example:







How do I do this with times? When I try, it won't work the same way it does with regular numbers:

 

MROUND(X,0) is going to round X to the nearest integer. Timecodes (at least the ones used here) treat each day as an increment of 1. So MROUND(X,0) if X is a time will give you midnight every time. You'll only see a difference if you format it to see the date.

But that is also why we are using MROUND instead of just ROUND. MROUND rounds to multiples of the second argument. So with 1440 minutes in a day you want MROUND(X,1/1440).

 

:)