I'd like to convert a column of numbers to be considered as seconds. Currently, when I divide the number by a duration (in time format), I get garbage (some large number of hours which doesn't make sense).

I want to convert the number (which is seconds but not formatted as seconds) so that I can divide it by a duration leading to a seconds per duration (say, seconds per hour).

Possible?

Can you just format both as number instead of time?
Time format isn't really handy for mathematical functions. It can work, but it confuses everybody on the entry.

Indeed, set it up in numerics, there's 3600 seconds/hour, just do the math.

Never liked the time format in Excel for doing much heavy lifting or custom stuff.

Agree with the others. Just leave it as numbers. If its already seconds, well and good. Otherwise you can create another column to do the math to convert to seconds.

Thanks guys.

This is an artifact of entering date / time stamps on my phone (using Numbers?) and then trying to manipulate the data in Excel on my Windows laptop.

Ironically, these calculations work on my stupid phone w/o data conversions / format conversions. Only issues on my PC. Problem is that it's tedious to manipulate data, enter formulas, etc on the phone.

Can you show an example of what you're trying to accomplish?

For example:

1) Row 77 is the cell with 42 divided by the cell with 108h 10m 48s. Not sure what the result of 223h means.

2) Row 78 is what I want where 0.31 (in sec per hour) is manually calculating 37 / 120 or 0.31s per hour.

I'm trying to track accuracy of my watch. I think it's improving over time (before, used to be about +12s per day, now it seems to be about +6s per day (breaking in?)). To determine if I should regulate or not.

quote:

Originally posted by konata88:
For example:

1) Row 77 is the cell with 42 divided by the cell with 108h 10m 48s. Not sure what the result of 223h means.

2) Row 78 is what I want where 0.31 (in sec per hour) is manually calculating 37 / 120 or 0.31s per hour.

I'm an Excel wizard but I need more information in order to help you.

I understand you're trying to track your watch accuracy but I need to understand what data you are using and how are you processing that data.

Assuming the date column is column A, what are the numbers in column B? It looks like time in 24 hour mode. What about columns C and D, what are they? If you click on any cell with a value in either C or D, what shows up in the formula bar (it's the line above the column letters)? While still selecting a cell in column C or D, hit Ctrl + i. A format cell window comes up and under the number tab, what category is highlighted - General, Time, or something else?

In column F, for where are those numbers coming from? is there a formula in those cells? What's the formula? What do those numbers represent?

You say 0.31 is sec per hour so what is 37 and why divide it by 120? What does 0.31 sec per hour represent - are you gaining 0.31 sec per hour?

Hi Rey,

col a: iphone function for date
col b: iphone function for time (actually, it seems like it's date time if you check the value of the cell, but formatted to just show time)
col c: delta time from previous time stamp (ie - row above)
col d: delta time from last last sync to atomic clock (ie - row 66 here)
col e: event description (manually typed)
col f: watch seconds ahead of atomic clock; for row 78, watch is 37 seconds ahead of 9/30 09:13)
Col g: 0.31 is 37 / 120 or 0.31 sec / hour; how many seconds per hour the watch is gaining. Or 7.5 sec per day. Row 77 col g - that was $F$77 / $D$77 - not sure why the result is 223h. 0.31 is 37 sec fast / 120h elapsed time since last sync to atomic clock. Yes, 0.31 is how many sec per hour the watch is gaining.

I think over the past month (ignoring shocks like going for a jog), the gain per day went from about 12s/day to currently about 7s/day.

quote:

Originally posted by konata88:
Hi Rey,

Okay, I figured it out. You can see I was able to jimmy your date, time, increment, time since Sync, and Delta Seconds.

If you look in the formula bar for cell D13 that displays 108:10:48, you'll see 1/4/1900 12:10:48 PM. All dates and times in Excel use 1/0/1900 as zero. Additionally, all dates and times are held in Excel as days. So when you divide Column E the Delta Seconds, Excel doesn't know it's seconds; it thinks it's days. So 42 days divided by 108 hours is 9.32 days and 9.32 days converts to 223.627 hours. If you convert .627 hours into minutes, you'll match the results you are showing.

The fix is you have to multiply column D by 24 hours; remember, Excel is carrying column D in its memory as days and fraction of days. So the result shows up in Column H as straight hours. The formula for Cell H13 is "= D13 * 24."

Then you divide column E (Delta seconds) by column H and you get Column I (seconds per hour).

Hi Rey - wow you spent some time figuring this out! You know some esoteric stuff about excel behavior.

I tried this out on the spreadsheet I have (excel converted from Numbers on my phone) and it didn't work for me. I think my col d is different from your re-entered data?

Maybe I can send my file to you to check it out?

konata88, coincidentally I made a spreadsheet to track this earlier this year. Just sent it to the email in your profile.

I'm an Excel dork so it even has a graph.

Cool! looks pretty good. One question: how are you entering the watch data in your column b? Manually, I'm guessing, in that time format?

I'm using the "Current Time" cell function in Numbers on the phone for the time stamp in column a. And manually entering number of seconds of offset on the watch. Instead of seconds, I could enter in a time stamp manually. That would work for your spreadsheet. I wonder if I can update my current data using a formula (add my offset to the time stamp and output to the time stamp format).

Yes, manually.

quote:

Originally posted by konata88:
Hi Rey - wow you spent some time figuring this out! You know some esoteric stuff about excel behavior.

I tried this out on the spreadsheet I have (excel converted from Numbers on my phone) and it didn't work for me. I think my col d is different from your re-entered data?

Maybe I can send my file to you to check it out?

Yes, you can send it to me. I'm thinking it's just a formatting issue on your end.

It would help if you explain how you're getting each data set. Are you downloading it to Excel? Or are you manually entering any number? I'm thinking the first three columns are downloaded because the time difference from the last time doesn't exactly match the straight math. Your excel file has smaller than seconds information that are not displayed.

My email is in my profile and I'll be on the lookout for it.

Also, did you know there's an app to track your watch's accuracy and you can just record the measurements instead? It's called Toolwatch.

I got it to work!!

I exported and emailed a new version of the file from phone to PC. I did something (not sure exactly what) to the Delta column and now the seconds per day formula is working as expected.

So, I can do what I want now! I'll go back and try again to see if I can determine exactly what I did differently. But at least now I know it's possible and I have a template. Looks like the delta to reset column is different - days/hours on the phone in Numbers but hours/minutes in Excel.

Thanks for the help!!