Excel and Time Calculations

w hole

w hole

Regular Member
Joined
Jan 1, 2000
Messages
591
Reaction score
0
Points
0
My Location
uk
Hello All

Can anyone tell me how to deduct 1 hour from times in an Excel Worksheet.

I have lots of data in hh:mm format with CET times and want to change it to GMT.


I have tried the obvious things and even consulted the Help, but just can't find a way...

Regards

W Hole
 
rolfw

rolfw

Believe it when I see it Admin.
Staff member
Joined
May 1, 1999
Messages
38,321
Reaction score
1,635
Points
113
My Satellite Setup
Technomate 5402 HD M2 Ci, DM7000s, Transparent 80cm Dish, Moteck SG2100 DiseqC motor, lots of legacy gear. Meters: Satlook Digital NIT, Promax HD Ranger+ spectrum analyser.
My Location
Berkshire
Try putting in an extra column with a global calculation subtracting a value of one from the CET column, it used to work in one of the old spread sheets i used.

rolf
 
w hole

w hole

Regular Member
Joined
Jan 1, 2000
Messages
591
Reaction score
0
Points
0
My Location
uk
Hello R.

Yes, that's what I'm trying to do, but I haven't been able to find the right entries to make..

Cheers

W Hole
 
w hole

w hole

Regular Member
Joined
Jan 1, 2000
Messages
591
Reaction score
0
Points
0
My Location
uk
The answer is : -0.041666

It seems that a day is 1.00 so divided by 12 = 0.041666.

I have tied it and it does seem to work.

The posting must have unlocked something in this little brain.

Thanks again for the help.

Regards

W Hole
 
rolfw

rolfw

Believe it when I see it Admin.
Staff member
Joined
May 1, 1999
Messages
38,321
Reaction score
1,635
Points
113
My Satellite Setup
Technomate 5402 HD M2 Ci, DM7000s, Transparent 80cm Dish, Moteck SG2100 DiseqC motor, lots of legacy gear. Meters: Satlook Digital NIT, Promax HD Ranger+ spectrum analyser.
My Location
Berkshire
Crumbs, I wouldn't have got there in a hundred years. LOL
It's obviously too long since I worked on a spread sheet. :)

rolf
 
w hole

w hole

Regular Member
Joined
Jan 1, 2000
Messages
591
Reaction score
0
Points
0
My Location
uk
That should of course be:

one day divided by *24* = 0.041666

I am still working on this because it won't work with 00:00 etc.

Will have a try with adding 23hrs rather than deducting 1hr.

Kind regards

W Hole
 
2old4this

2old4this

Honorary Admin
Joined
Jan 1, 1999
Messages
1,658
Reaction score
0
Points
0
My Location
Cloud Cuckoo Land
Blimey you're making it difficult lads...

Let's assume that cell A6 contains a time.
- Go to the cell where you want to put the re-calculated time
- format the cell as time hh:mm
- enter into it the following: =A6-"01:00"
To subtract two hours it would be =A6-"02:00"
and so on.

2old
 
rolfw

rolfw

Believe it when I see it Admin.
Staff member
Joined
May 1, 1999
Messages
38,321
Reaction score
1,635
Points
113
My Satellite Setup
Technomate 5402 HD M2 Ci, DM7000s, Transparent 80cm Dish, Moteck SG2100 DiseqC motor, lots of legacy gear. Meters: Satlook Digital NIT, Promax HD Ranger+ spectrum analyser.
My Location
Berkshire
I was just about to suggest that 2old :7

Really I was.

Rolf
 
w hole

w hole

Regular Member
Joined
Jan 1, 2000
Messages
591
Reaction score
0
Points
0
My Location
uk
Hello All

Yes it has the same effect at the entry I was using, problem is it won't subtract an hour from anything between 00:00 and 00:59.

I tried adding 23hrs, it gives the correct value in the cell, but because it actually stores a date and time even though only the time is displayed, it messes up the sorting.

I'm sure the answer is in there somewhere, even if it means sorting on the original times rather than the conversions, but it looks like a bit more trial and error, unless someone knows better.

Thanks again for the input, must get off to see Letterman, then early start Friday, so will have another think tomorrow.

Regards

W Hole.

PS. 2old, any idea why Letterman is no longer on V8?
 
Top