Hi everyone, Is there anyone that can help me with the Excel Date/Time stamp? It reads as: Format this column as a date to get timezone-converted datestamps. The timestamp format is in UTC milliseconds since 1/1/1970 00:00:00.
I'm not the brigtest crayon in the box when it comes to this sort of knowledge or computers in general for that matter. Ill see if i can attach a sample of what I'm talking about here.
Thank you!
Walt
In Excel you'll need to highlight the timestamp row by clicking on the column header (column B in this case I think). Then right-click and select "Format Cells" and under the "Numbers" tab you should have a list of possible formats. I suggest formatting as "Time" in the "Category" list and select your preferred display options from the "Format" list. I haven't used excel in donkey's years so this might need a little tweaking. However, these instructions will work for Libre Office Calc.
Thank you Andrew!
I did try as you explained in column (B) but wasn't able to find the proper format so I tried it on column (A) and was able to get all I feel is needed to review the daily report. 8) I don't feel Ill worry with the time stamp for now anyways ;) Ill attach what I was able to come up with, Thank you again!
Walt
Quote from: BobWhite on March 25, 2016, 07:20:25 AM
Thank you Andrew!
I did try as you explained in column (B) but wasn't able to find the proper format so I tried it on column (A) and was able to get all I feel is needed to review the daily report. 8) I don't feel Ill worry with the time stamp for now anyways ;) Ill attach what I was able to come up with, Thank you again!
Walt
You are on the right path. Column A is the one you want to format not B.
Cell B2 needs to have the offset hours from UTC. It the same value you use to set your computer Clock offset. For example PDT has an offset value of -7.
There is also an error in Andrew’s formula that has not been fixed. Here’s the quote from my post on March 16, 2015:
“Couple things to remember when dumping the Live Data from the Local App into Excel. The first one is Cell B2 is the for UTC timezone Offset (GMT offset -- e.g. US PST time is GMT-7 so use -7. Brisbane Australia is GMT+10 so use 10).
Then you want to format all cells in column A as TIME this will give you the correct hours minutes and seconds for each reading.
There are a couple of errors in the way the time formulas are presented in column A by Andrew’s LA.
The first error is a bad formula in cell A4 =(B5/1000 +$B$2*60*60)/86400 + 25569
The correct formula is
=(B4/1000 + $B$2*60*60)/86400 + 25569
All formulas is column A are off by one row and you need to replicate the corrected A4 down through all cells in column A that have data. Doing this will get rid of a second error that occurs further down is column A. I’m not sure it occurs in the same place but the above formula will start over again with B5. In the case I’m looking at now this occurred in cell A12801. You will see it if you are looking at the time in column A and have change the cells to represent TIME because the time will most likely jump from PM back to AM if you were collecting data from the morning until the evening.â€
Quote from: Resthome on March 28, 2016, 05:29:09 PM
Quote from: BobWhite on March 25, 2016, 07:20:25 AM
Thank you Andrew!
I did try as you explained in column (B) but wasn't able to find the proper format so I tried it on column (A) and was able to get all I feel is needed to review the daily report. 8) I don't feel Ill worry with the time stamp for now anyways ;) Ill attach what I was able to come up with, Thank you again!
Walt
You are on the right path. Column A is the one you want to format not B.
Cell B2 needs to have the offset hours from UTC. It the same value you use to set your computer Clock offset. For example PDT has an offset value of -7.
There is also an error in Andrew’s formula that has not been fixed. Here’s the quote from my post on March 16, 2015:
“Couple things to remember when dumping the Live Data from the Local App into Excel. The first one is Cell B2 is the for UTC timezone Offset (GMT offset -- e.g. US PST time is GMT-7 so use -7. Brisbane Australia is GMT+10 so use 10).
Then you want to format all cells in column A as TIME this will give you the correct hours minutes and seconds for each reading.
There are a couple of errors in the way the time formulas are presented in column A by Andrew’s LA.
The first error is a bad formula in cell A4
=(B5/1000 +$B$2*60*60)/86400 + 25569
The correct formula is
=(B4/1000 + $B$2*60*60)/86400 + 25569
All formulas is column A are off by one row and you need to replicate the corrected A4 down through all cells in column A that have data. Doing this will get rid of a second error that occurs further down is column A. I’m not sure it occurs in the same place but the above formula will start over again with B5. In the case I’m looking at now this occurred in cell A12801. You will see it if you are looking at the time in column A and have change the cells to represent TIME because the time will most likely jump from PM back to AM if you were collecting data from the morning until the evening.â€
Thank you RH,
I will try to look into it more this weekend or next week, a bit tied up for now but still trying to keep up with posts.
Walt
John,
I corrected the date formula in column A as you said and dragged it down to bottom and pasted it in so that seems good .
I am a bit stuck on how to format column B to get the time to show
I change 0 at top to -6 for my central time offset , also tried formatting the whole column to time but I only get ################ when I do that. Maybe too many digits to show ?
(//)
Larry
Quote from: ClassicCrazy on January 16, 2017, 11:56:23 AM
John,
I corrected the date formula in column A as you said and dragged it down to bottom and pasted it in so that seems good .
I am a bit stuck on how to format column B to get the time to show
I change 0 at top to -6 for my central time offset , also tried formatting the whole column to time but I only get ################ when I do that. Maybe too many digits to show ?
(//)
Larry
Larry
Yeah in Excel the ###s mean the column isn't wide enough. Just click on the right hand line on the header between column A and B and drag it to the right to widen column A.
In your example I see the DATE, change the format for column A to TIME and select the format that show seconds.
Actually, if you get the mouse pointer 'right on' the line and double click it will auto size and you can 'squeeze it' if you don't like what it thinks is correct... ;)
I changed column A to time in seconds . Do I need to format column B ? Doesn't seem like anything I try ever fills any numbers in there. And making the column larger does not seem to help either.
(//)
Larry,
I usually just hide column B. It's used to calculated the time in Column A but doesn't have any real meaning. Just select the whole column B and right click and select HIDE.
Looks like you are on your way, good job.
It appears that the program (Local App) really isn't 100 % compatible with Excel!
Quote from: Resthome on January 16, 2017, 03:01:59 PM
Larry,
I usually just hide column B. It's used to calculated the time in Column A but doesn't have any real meaning. Just select the whole column B and right click and select HIDE.
Looks like you are on your way, good job.
Okay - got it ! But what is all that blurb about putting in your offset from UTC to local time ?
I am watching some video's on youtube now trying to learn some basic graphing.
Larry
Quote from: BobWhite on January 16, 2017, 03:08:21 PM
It appears that the program (Local App) really isn't 100 % compatible with Excel!
Yeah there was a error in the formula that reference an incorrect cell, but other than that it works like any other CSV file.
Quote from: ClassicCrazy on January 16, 2017, 03:30:21 PM
Quote from: Resthome on January 16, 2017, 03:01:59 PM
Larry,
I usually just hide column B. It's used to calculated the time in Column A but doesn't have any real meaning. Just select the whole column B and right click and select HIDE.
Looks like you are on your way, good job.
Okay - got it ! But what is all that blurb about putting in your offset from UTC to local time ?
I am watching some video's on youtube now trying to learn some basic graphing.
Larry
Just some instructions that you have to put the off-set value from GMT UTC since the time is calculated to your local time zone. That cell is used in the formula in column A. You can hide it but you should not delete it or the formula in A will be in error because it can't find that value.
Hi,
Quote from: ClassicCrazy on January 16, 2017, 11:56:23 AM
I corrected the date formula in column A as you said and dragged it down to bottom and pasted it in so that seems good.
You can do it by: Ctrl-C to copy, Shift-Ctrl-Down, that highlights the whole column of data, then Ctrl-V to paste. I do it with a macro (time zone entry, cell error correction, copy/paste, & date/time format column).
Still, annoying after all these years.
dRdoS7
Quote from: dRdoS7 on January 17, 2017, 06:31:53 PM
Hi,
Quote from: ClassicCrazy on January 16, 2017, 11:56:23 AM
I corrected the date formula in column A as you said and dragged it down to bottom and pasted it in so that seems good.
You can do it by: Ctrl-C to copy, Shift-Ctrl-Down, that highlights the whole column of data, then Ctrl-V to paste. I do it with a macro (time zone entry, cell error correction, copy/paste, & date/time format column).
Still, annoying after all these years.
dRdoS7
I did learn some those Control Shift arrow tricks watching a youtube video the other day . Still have a ways to go on the graphing part though !
I watched part of an Excel course and made some progress in graphing the Whizbang amps.
But I am sort of stuck on how to make my graph ( chart as they call it ) with better resolution. For example I would like to bring the bottom up from 0 to 10 amps and the top down to around 18 amps .
Is there an easy way to do that either in chart of any other way ?
John I see what you were talking about the noise - so today I had moved my ending amps from 10.8 up to around 11.2 . So I am hoping I could better analyze what it is doing from the chart .
So I am getting there !
(//)
Larry
Quote from: ClassicCrazy on February 23, 2017, 02:41:53 AM
I watched part of an Excel course and made some progress in graphing the Whizbang amps.
But I am sort of stuck on how to make my graph ( chart as they call it ) with better resolution. For example I would like to bring the bottom up from 0 to 10 amps and the top down to around 18 amps .
Is there an easy way to do that either in chart of any other way ?
John I see what you were talking about the noise - so today I had moved my ending amps from 10.8 up to around 11.2 . So I am hoping I could better analyze what it is doing from the chart .
So I am getting there !
(//)
Larry
Click on the scale on the left until it is highlighted then right click on it and select Format Axis..
You can then change the MAX and MIN to Fixed values.
You can also add Minor lines by selecting Format Minor Axis after the doing the first step again.
Does that help?
Larry
Thanks John - yes that helped !
I knew there must be an easy way to do it .
Larry