Date/Time stamp formate in Excel

[BobWhite]

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

[atop8918]

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.

[BobWhite]

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. 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

[Resthome]

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. 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.”

[BobWhite]

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. 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

[ClassicCrazy]

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

[Resthome]

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.

[Westbranch]

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...

[ClassicCrazy]

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.

[Resthome]

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.

[BobWhite]

It appears that the program (Local App) really isn't 100 % compatible with Excel!

[ClassicCrazy]

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

[Resthome]

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.

[Resthome]

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.

[dRdoS7]

Hi,

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