Another date handling question variant

  • Thread starter Thread starter stus
  • Start date Start date
S

stus

Hi,

I want to specify time+date and adjust them by timezone, but cannot see
how to do this without laborious formulae. I've seen the other
time/data tips on this site (v.good thanks), but they don't seem to
cover this scenario.

what I want to do:

Column1: Time,
format: dd-mmm-yy [hh]:mm
eg: 26-feb-04 03:46

Column2: TZ offset,
format: num (0 DP)
eg: 5

Column3: Adjusted Time,
format: dd-mmm-yy [hh]:mm
formula: Col1 + (Col2/24)
eg: 26-feb-04 08:46

ie. I want the basic number of TZ offset to increment the original time
but that number of hours.

Of course, it doesn't work, the existence of the dd-mmm-yy info seems
to affect the hh field.

Yes, I also want it to accept negative values of column 2.

I'd have thought Excel would allow you to add such fields like that, by
specifying which component of the date (eg. hh) is to be increased by
that amount. Not sure if the []'s are trying to do that.

TIA, sTu.
 
Hi

Just get rid of the [ ] in your format, they are not necessary.
Then with original time in A1 and hour soffset in B1
=A1+B1/2
produces the correct results with both positive and negative values in B1
 
That should have read
=A1+B1/24

--
Regards
Roger Govier
Roger Govier said:
Hi

Just get rid of the [ ] in your format, they are not necessary.
Then with original time in A1 and hour soffset in B1
=A1+B1/2
produces the correct results with both positive and negative values in B1

--
Regards
Roger Govier
stus > said:
Hi,

I want to specify time+date and adjust them by timezone, but cannot see
how to do this without laborious formulae. I've seen the other
time/data tips on this site (v.good thanks), but they don't seem to
cover this scenario.

what I want to do:

Column1: Time,
format: dd-mmm-yy [hh]:mm
eg: 26-feb-04 03:46

Column2: TZ offset,
format: num (0 DP)
eg: 5

Column3: Adjusted Time,
format: dd-mmm-yy [hh]:mm
formula: Col1 + (Col2/24)
eg: 26-feb-04 08:46

ie. I want the basic number of TZ offset to increment the original time
but that number of hours.

Of course, it doesn't work, the existence of the dd-mmm-yy info seems
to affect the hh field.

Yes, I also want it to accept negative values of column 2.

I'd have thought Excel would allow you to add such fields like that, by
specifying which component of the date (eg. hh) is to be increased by
that amount. Not sure if the []'s are trying to do that.

TIA, sTu.
Click to expand...
Click to expand...
 
1. The [ ] force Excel to display hours greater than 24. It is used for
example, by runners who want to add up a column of hh:mm and see the total
number of hours displayed. They don't want it spilling over into days. So
that's part of your problem.

2. When you do math with times, you have to convert your number into hours.
Excel stores times as fractions of a day. So 1 hour is 1/24th of a day. If
your date is in A1, and the time zone difference is, for example, 5 in A2,
your formula is:

=a1+a2/24

Format as dd-mmm-yy hh:mm, no brackets. Will work whether TZ is negative or
positive.
 
Back
Top