Need help adjusting time

P

Pete_UK

Times are stored in Excel as fractions of a 24-hour day. Thus, 12
hours is actually 0.5, 6 hours is 0.25 etc. If you want to add x hours
onto a time in A1, then you can do this:

=A1 + x/24

You need to format the cell as a time (maybe [h]:mm:ss), but be aware
that if the addition takes the time beyond midnight then the time will
not wrap at 24 hours with this setting.

Hope this helps.

Pete
 
B

Billy Liddel

Jim

Pete gave a general answer because the question was rather vague. Perhaps
you can post concrete examples of the data?

Peter

Jim said:
Well, o.k., but can you provide any help with my time adjustment per/sample
problem?

Jim



Times are stored in Excel as fractions of a 24-hour day. Thus, 12
hours is actually 0.5, 6 hours is 0.25 etc. If you want to add x hours
onto a time in A1, then you can do this:

=A1 + x/24

You need to format the cell as a time (maybe [h]:mm:ss), but be aware
that if the addition takes the time beyond midnight then the time will
not wrap at 24 hours with this setting.

Hope this helps.

Pete

I am trying to figure out how to develop a cell formula that will adjust the
time on entered data.

I use Hobo data loggers to collect temperatures every 4 hours from October
to May. I set the time on the logger at launch according to a WWVB radio
clock, hopefully to the nearest second or so.

On recovery, I record the actual WWVB time as compared to the logger time,
and not unexpectidly, there is always some descepency. The data, which
includes the date and time of each record is loaded into an Excel
spreadsheet.

I would like to be able to create a column of cells beside the loggers'
time, that I will call actual time, based on the time difference at
recovery, and the logging period since launch. I can't seem to get my head
to work this out.

Any ideas on the cell formula that will do this?

Thanks
Jim
 
B

Billy Liddel

However, maybe this will help.
c d f
Data T Loaded Difference
05:25 10:00 04:35
22:25 02:00 03:35

=IF(D15<C15,(D15+1)-C15,D15-C15)


HTH
Peter

Jim said:
Well, o.k., but can you provide any help with my time adjustment per/sample
problem?

Jim



Times are stored in Excel as fractions of a 24-hour day. Thus, 12
hours is actually 0.5, 6 hours is 0.25 etc. If you want to add x hours
onto a time in A1, then you can do this:

=A1 + x/24

You need to format the cell as a time (maybe [h]:mm:ss), but be aware
that if the addition takes the time beyond midnight then the time will
not wrap at 24 hours with this setting.

Hope this helps.

Pete

I am trying to figure out how to develop a cell formula that will adjust the
time on entered data.

I use Hobo data loggers to collect temperatures every 4 hours from October
to May. I set the time on the logger at launch according to a WWVB radio
clock, hopefully to the nearest second or so.

On recovery, I record the actual WWVB time as compared to the logger time,
and not unexpectidly, there is always some descepency. The data, which
includes the date and time of each record is loaded into an Excel
spreadsheet.

I would like to be able to create a column of cells beside the loggers'
time, that I will call actual time, based on the time difference at
recovery, and the logging period since launch. I can't seem to get my head
to work this out.

Any ideas on the cell formula that will do this?

Thanks
Jim
 
J

Jim

I am trying to figure out how to develop a cell formula that will adjust the
time on entered data.

I use Hobo data loggers to collect temperatures every 4 hours from October
to May. I set the time on the logger at launch according to a WWVB radio
clock, hopefully to the nearest second or so.

On recovery, I record the actual WWVB time as compared to the logger time,
and not unexpectidly, there is always some descepency. The data, which
includes the date and time of each record is loaded into an Excel
spreadsheet.

I would like to be able to create a column of cells beside the loggers'
time, that I will call actual time, based on the time difference at
recovery, and the logging period since launch. I can't seem to get my head
to work this out.

Any ideas on the cell formula that will do this?

Thanks
Jim
 
J

Jim

Well, o.k., but can you provide any help with my time adjustment per/sample
problem?

Jim



Times are stored in Excel as fractions of a 24-hour day. Thus, 12
hours is actually 0.5, 6 hours is 0.25 etc. If you want to add x hours
onto a time in A1, then you can do this:

=A1 + x/24

You need to format the cell as a time (maybe [h]:mm:ss), but be aware
that if the addition takes the time beyond midnight then the time will
not wrap at 24 hours with this setting.

Hope this helps.

Pete
 
J

JoeU2004

Jim said:
Isn't this the job of computers and Excel anyway?

Generally not a good idea to "bite the hand that feeds you" -- get snippity
with people who are struggling to help you. Yes, computers can solve almost
any problem. But the solution is only as good as the problem description.
Billy was saying that your original problem description was not sufficiently
clear. I agree. Concrete examples always help clarify the problem, as you
finally did.

You are talking about adjusting for clock skew -- the fact that time on the
Hobo logger might not stay in sync with system time over a long period of
time. That is what I thought you might be talking about; but I was not
sure.

Label the first log entry as "firstLog", the last log entry as "lastLog",
and the corresponding system time as "lastActual".

Then the average skew per day (and any fraction thereof) is:

=(lastActual - lastLog) / (lastLog - firstLog)

Caveat: Format that as Number with 6 decimal places. See below if you want
to format as "[h]:mm:ss.000".

Label that cell as "skew".

Then if the "firstLog" is in A1, put the following formula into B1 and copy
down:

=A1 + skew * (A1 - firstLog)

Caveat: If you actually name the cells as I have suggested, Excel might
"autocorrect" A1 to firstLog. If that happens, manually edit the formula in
the Formula Bar to use A1.


PS: If you want to format skew as "[h]:mm:ss.000", there is an issue with
negative skew. Instead of changing the date system to 1904, as some people
suggest, I suggest that you put the following formula into another cell:

=IF(skew < 0, "-", "") & TEXT(ABS(skew), "[h]:mm:ss.000")

and format with Right horizontal alignment. Do something similar if you
want format the total skew for the last log entry (i.e =lastActual -
lastLog). Note that these cells are not numeric; so they cannot be used in
arithmetic formulas such as the "skew" formula above.


PPS: You could compute the skew per log entry instead. IMHO, the advantage
of computing the skew per (fractional) day as I did is: it compensates for
if the logger entry times are not exactly X hours/minutes/seconds apart.
But if that's not an issue, then:

skew: =(lastActual - lastLog) / (COUNT(firstLog:lastLog) - 1)

B1: =A1 + skew * (ROW(A1) - ROW(firstLog))


----- original message -----

Jim said:
Sure, here is info on one real life example:

From 11/14/08 to 4/5/09 there were 1704 samples recorded (every 2 hours in
this case). That's 3408 hours of recording time. The logger records the
date and time of each record. The time of the first sample on offload of
the data, was the same as actual time, because I set the time accurately
on
launch.

The time of sample #1704 was off by 22 minutes 10 seconds (logger time
compared to actual time).

Obviously, the beginning sample times were close to the actual time, but
as
time went on, the sample time was further from the actual time.

So my problem is to use a formula that will compute and distribute the
time
difference error across the entire 1704 sample, 4 1/2 month logging
period......and fill the cells in a column with the actual time of each
sample. I could probably do this by hand, but for multiple spreadsheets
of
varying numbers of samples, using excel, rather than my pencil would be a
better solution. Isn't this the job of computers and Excel anyway?

Thanks
Jim





Billy Liddel said:
Jim

Pete gave a general answer because the question was rather vague. Perhaps
you can post concrete examples of the data?

Peter

Jim said:
Well, o.k., but can you provide any help with my time adjustment per/sample
problem?

Jim



Times are stored in Excel as fractions of a 24-hour day. Thus, 12
hours is actually 0.5, 6 hours is 0.25 etc. If you want to add x hours
onto a time in A1, then you can do this:

=A1 + x/24

You need to format the cell as a time (maybe [h]:mm:ss), but be aware
that if the addition takes the time beyond midnight then the time will
not wrap at 24 hours with this setting.

Hope this helps.

Pete

I am trying to figure out how to develop a cell formula that will adjust
the
time on entered data.

I use Hobo data loggers to collect temperatures every 4 hours from October
to May. I set the time on the logger at launch according to a WWVB radio
clock, hopefully to the nearest second or so.

On recovery, I record the actual WWVB time as compared to the logger time,
and not unexpectidly, there is always some descepency. The data,
which
includes the date and time of each record is loaded into an Excel
spreadsheet.

I would like to be able to create a column of cells beside the loggers'
time, that I will call actual time, based on the time difference at
recovery, and the logging period since launch. I can't seem to get my head
to work this out.

Any ideas on the cell formula that will do this?

Thanks
Jim
 
J

JoeU2004

PS....
Then the average skew per day (and any fraction thereof) is:
=(lastActual - lastLog) / (lastLog - firstLog)

I meant to say that all of my formulas assume that the log data is recorded
in the Excel worksheet as a bona fide date/time serial number.

Double-check by clicking on Format > Cells and confirm that the format is
Date, Time or a custom format that is something like d/mm/yyyy h:mm:ss.


----- original message -----

JoeU2004 said:
Jim said:
Isn't this the job of computers and Excel anyway?

Generally not a good idea to "bite the hand that feeds you" -- get
snippity with people who are struggling to help you. Yes, computers can
solve almost any problem. But the solution is only as good as the problem
description. Billy was saying that your original problem description was
not sufficiently clear. I agree. Concrete examples always help clarify
the problem, as you finally did.

You are talking about adjusting for clock skew -- the fact that time on
the Hobo logger might not stay in sync with system time over a long period
of time. That is what I thought you might be talking about; but I was not
sure.

Label the first log entry as "firstLog", the last log entry as "lastLog",
and the corresponding system time as "lastActual".

Then the average skew per day (and any fraction thereof) is:

=(lastActual - lastLog) / (lastLog - firstLog)

Caveat: Format that as Number with 6 decimal places. See below if you
want to format as "[h]:mm:ss.000".

Label that cell as "skew".

Then if the "firstLog" is in A1, put the following formula into B1 and
copy down:

=A1 + skew * (A1 - firstLog)

Caveat: If you actually name the cells as I have suggested, Excel might
"autocorrect" A1 to firstLog. If that happens, manually edit the formula
in the Formula Bar to use A1.


PS: If you want to format skew as "[h]:mm:ss.000", there is an issue with
negative skew. Instead of changing the date system to 1904, as some
people suggest, I suggest that you put the following formula into another
cell:

=IF(skew < 0, "-", "") & TEXT(ABS(skew), "[h]:mm:ss.000")

and format with Right horizontal alignment. Do something similar if you
want format the total skew for the last log entry (i.e =lastActual -
lastLog). Note that these cells are not numeric; so they cannot be used
in arithmetic formulas such as the "skew" formula above.


PPS: You could compute the skew per log entry instead. IMHO, the
advantage of computing the skew per (fractional) day as I did is: it
compensates for if the logger entry times are not exactly X
hours/minutes/seconds apart. But if that's not an issue, then:

skew: =(lastActual - lastLog) / (COUNT(firstLog:lastLog) - 1)

B1: =A1 + skew * (ROW(A1) - ROW(firstLog))


----- original message -----

Jim said:
Sure, here is info on one real life example:

From 11/14/08 to 4/5/09 there were 1704 samples recorded (every 2 hours
in
this case). That's 3408 hours of recording time. The logger records the
date and time of each record. The time of the first sample on offload of
the data, was the same as actual time, because I set the time accurately
on
launch.

The time of sample #1704 was off by 22 minutes 10 seconds (logger time
compared to actual time).

Obviously, the beginning sample times were close to the actual time, but
as
time went on, the sample time was further from the actual time.

So my problem is to use a formula that will compute and distribute the
time
difference error across the entire 1704 sample, 4 1/2 month logging
period......and fill the cells in a column with the actual time of each
sample. I could probably do this by hand, but for multiple spreadsheets
of
varying numbers of samples, using excel, rather than my pencil would be a
better solution. Isn't this the job of computers and Excel anyway?

Thanks
Jim





Billy Liddel said:
Jim

Pete gave a general answer because the question was rather vague.
Perhaps
you can post concrete examples of the data?

Peter

:

Well, o.k., but can you provide any help with my time adjustment per/sample
problem?

Jim



Times are stored in Excel as fractions of a 24-hour day. Thus, 12
hours is actually 0.5, 6 hours is 0.25 etc. If you want to add x hours
onto a time in A1, then you can do this:

=A1 + x/24

You need to format the cell as a time (maybe [h]:mm:ss), but be aware
that if the addition takes the time beyond midnight then the time will
not wrap at 24 hours with this setting.

Hope this helps.

Pete

I am trying to figure out how to develop a cell formula that will adjust
the
time on entered data.

I use Hobo data loggers to collect temperatures every 4 hours from October
to May. I set the time on the logger at launch according to a WWVB radio
clock, hopefully to the nearest second or so.

On recovery, I record the actual WWVB time as compared to the logger time,
and not unexpectidly, there is always some descepency. The data,
which
includes the date and time of each record is loaded into an Excel
spreadsheet.

I would like to be able to create a column of cells beside the loggers'
time, that I will call actual time, based on the time difference at
recovery, and the logging period since launch. I can't seem to get
my head
to work this out.

Any ideas on the cell formula that will do this?

Thanks
Jim
 
J

Jim

Sure, here is info on one real life example:

From 11/14/08 to 4/5/09 there were 1704 samples recorded (every 2 hours in
this case). That's 3408 hours of recording time. The logger records the
date and time of each record. The time of the first sample on offload of
the data, was the same as actual time, because I set the time accurately on
launch.

The time of sample #1704 was off by 22 minutes 10 seconds (logger time
compared to actual time).

Obviously, the beginning sample times were close to the actual time, but as
time went on, the sample time was further from the actual time.

So my problem is to use a formula that will compute and distribute the time
difference error across the entire 1704 sample, 4 1/2 month logging
period......and fill the cells in a column with the actual time of each
sample. I could probably do this by hand, but for multiple spreadsheets of
varying numbers of samples, using excel, rather than my pencil would be a
better solution. Isn't this the job of computers and Excel anyway?

Thanks
Jim





Billy Liddel said:
Jim

Pete gave a general answer because the question was rather vague. Perhaps
you can post concrete examples of the data?

Peter

Jim said:
Well, o.k., but can you provide any help with my time adjustment per/sample
problem?

Jim



Times are stored in Excel as fractions of a 24-hour day. Thus, 12
hours is actually 0.5, 6 hours is 0.25 etc. If you want to add x hours
onto a time in A1, then you can do this:

=A1 + x/24

You need to format the cell as a time (maybe [h]:mm:ss), but be aware
that if the addition takes the time beyond midnight then the time will
not wrap at 24 hours with this setting.

Hope this helps.

Pete

I am trying to figure out how to develop a cell formula that will
adjust
the
time on entered data.

I use Hobo data loggers to collect temperatures every 4 hours from October
to May. I set the time on the logger at launch according to a WWVB radio
clock, hopefully to the nearest second or so.

On recovery, I record the actual WWVB time as compared to the logger time,
and not unexpectidly, there is always some descepency. The data, which
includes the date and time of each record is loaded into an Excel
spreadsheet.

I would like to be able to create a column of cells beside the loggers'
time, that I will call actual time, based on the time difference at
recovery, and the logging period since launch. I can't seem to get my head
to work this out.

Any ideas on the cell formula that will do this?

Thanks
Jim
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top