Have imput the entire WC schedule from online site into my EXCEL file (found many errors). Header columns are: DATE/TIME/M/F/EVENT/ROUND. All times are BERLIN times. Is there a shortcut way I can subtract 7 hours to make the times Central time? Don't want to go row by row and change the times. Can this be done in EXCEL using some type of sum function? Any help would be appreciated.
Announcement
Collapse
No announcement yet.
Any EXCEL gurus out there?
Collapse
Unconfigured Ad Widget
Collapse
X
-
Insert two new columns next to the column of times. In the first new column, fill it with 7:00. In the second new column use the function =A1-B1, etc., as follows:
22:30 7:00 15:30 (=A1-B1)
20:00 7:00 13:00 (=A2-B2)
19:30 7:00 12:30 (=A3-B3)
This should work. You can then convert the third column, using Copy, Paste Special, Values. Then you can delete the first and second column, and you'll be left with a column of Central Times.
All of our Olympic database started out in Excel so I've used it quite a bit over the years.
Comment
-
Originally posted by bambamInsert two new columns next to the column of times. In the first new column, fill it with 7:00. In the second new column use the function =A1-B1, etc., as follows:
22:30 7:00 15:30 (=A1-B1)
20:00 7:00 13:00 (=A2-B2)
19:30 7:00 12:30 (=A3-B3)
This should work. You can then convert the third column, using Copy, Paste Special, Values. Then you can delete the first and second column, and you'll be left with a column of Central Times.
All of our Olympic database started out in Excel so I've used it quite a bit over the years.
"2009 WORLD CHAMPIONSHIPS
(15-23 AUGUST) "
DATE "S.A.
TIME" "BERLIN
TIME" MEN/WOMEN EVENT ROUND
15 305 1005 MEN SHOT PUT Q
15 310 1010 WOMEN 100 HURDLES HEP
15 350 1050 WOMEN 3000 STEEPLE 1-2
15 400 1100 WOMEN TRIPLE JUMP Q
15 420 1120 WOMEN HIGH JUMP HEP
15 440 1140 MEN 100 1-4
15 500 1200 MEN HAMMER THROW Q
15 550 1250 WOMEN 400 1-3
15 600 1300 MEN 20K RACE WALK FINAL
15 1115 1815 MEN 1500 1-3
15 1120 1820 WOMEN SHOT PUT HEP
15 1150 1850 MEN 100 2-4
15 1200 1900 WOMEN POLE VAULT Q
15 1225 1925 WOMEN 10,000 FINAL
15 1315 2015 MEN SHOT PUT FINAL
15 1320 2020 MEN 400 HURDLES 1-3
15 1410 2110 WOMEN 200 HEP
16 305 1005 WOMEN SHOT PUT Q
16 310 1010 WOMEN 800 1-3
16 345 1045 WOMEN JAVELIN Q
16 400 1100 MEN 3000 STEEPLE 1-2
16 435 1135 WOMEN LONG JUMP HEP
16 455 1155 WOMEN 100 1-4
16 500 1200 WOMEN 20K RACE WALK FINAL
16 1105 1805 WOMEN JAVELIN HEP
16 1135 1835 WOMEN 100 2-4
16 1200 1900 MEN TRIPLE JUMP Q
16 1210 1910 MEN 100 3-4
16 1240 1940 WOMEN 400 2-3
16 1315 2015 MEN 400 HURDLES 2-3
16 1320 2020 WOMEN SHOT PUT FINAL
16 1350 2050 WOMEN 800 HEP
16 1435 2135 MEN 100 FINAL
17 1105 1805 MEN HAMMER THROW FINAL
17 1115 1815 WOMEN 400 HURDLES 1-3
17 1145 1845 WOMEN POLE VAULT FINAL
17 1205 1905 WOMEN 100 3-4
17 1230 1930 WOMEN 800 2-3
17 1300 2000 WOMEN TRIPLE JUMP FINAL
17 1310 2010 MEN 1500 2-3
17 1330 2030 WOMEN 3000 STEEPLE FINAL
17 1350 2050 MEN 10,000 FINAL
17 1435 2135 WOMEN 100 FINAL
18 305 1005 MEN 200 1-4
18 310 1010 MEN DISCUS Q
18 320 1020 WOMEN HIGH JUMP Q
18 405 1105 MEN 400 1-3
18 505 1205 WOMEN 1500 1-3
18 1105 1805 MEN TRIPLE JUMP FINAL
18 1110 1810 WOMEN 100 HURDLES 1-3
18 1155 1855 MEN 200 2-4
18 1225 1925 WOMEN JAVELIN FINAL
18 1235 1935 WOMEN 400 FINAL
18 1250 1950 MEN 3000 STEEPLE FINAL
18 1315 2015 WOMEN 400 HURDLES 2-3
18 1350 2050 MEN 400 HURDLES FINAL
19 305 1005 MEN 100 DEC
19 310 1010 WOMEN DISCUS Q
19 345 1045 WOMEN 5000 1-2
19 400 1100 MEN HIGH JUMP Q
19 415 1115 MEN LONG JUMP DEC
19 435 1135 MEN 110 HURDLES 1-3
19 510 1210 WOMEN 200 1-4
19 600 1300 MEN SHOT PUT DEC
19 1105 1805 MEN HIGH JUMP DEC
19 1115 1815 MEN 400 2-3
19 1145 1845 WOMEN 100 HURDLES 2-3
19 1225 1925 MEN 200 3-4
19 1245 1945 WOMEN 200 2-4
19 1310 2010 MEN DISCUS FINAL
19 1325 2025 MEN 1500 FINAL
19 1345 2045 MEN 400 DEC
19 1415 2115 WOMEN 100 HURDLES FINAL
19 1435 2135 WOMEN 800 FINAL
20 305 1005 MEN 110 HURDLES DEC
20 310 1010 MEN POLE VAULT Q
20 410 1110 MEN DISCUS DEC
20 445 1145 MEN 800 1-3
20 645 1345 WOMEN HAMMER THROW Q
20 725 1425 MEN POLE VAULT DEC
20 1105 1805 MEN JAVELIN DEC
20 1110 1810 MEN LONG JUMP Q
20 1115 1815 MEN 110 HURDLES 2-3
20 1155 1855 MEN 5000 1-2
20 1210 1910 WOMEN HIGH JUMP FINAL
20 1250 1950 WOMEN 200 3-4
20 1315 2015 WOMEN 400 HURDLES FINAL
20 1335 2035 MEN 200 FINAL
20 1355 2055 MEN 110 HURDLES FINAL
20 1415 2115 MEN 1500 DEC
21 210 0910 MEN 50 K RACE WALK FINAL
21 1005 1705 MEN JAVELIN Q
21 1100 1800 WOMEN LONG JUMP Q
21 1215 1915 MEN HIGH JUMP FINAL
21 1230 1930 MEN 4 X 100 RELAY 1-2
21 1300 2000 WOMEN 1500 2-3
21 1320 2020 WOMEN DISCUS FINAL
21 1330 2030 MEN 800 2-3
21 1400 2100 WOMEN 200 FINAL
21 1420 2120 MEN 400 FINAL
22 445 1145 MEN MARATHON FINAL
22 1105 1805 MEN LONG JUMP FINAL
22 1110 1810 WOMEN 4 X 100 RELAY 1-2
22 1115 1815 MEN POLE VAULT FINAL
22 1155 1855 MEN 4 X 400 RELAY 1-2
22 1230 1930 WOMEN HAMMER THROW FINAL
22 1235 1935 WOMEN 5000 FINAL
22 1300 2000 WOMEN 4 X 100 RELAY FINAL
22 1315 2015 WOMEN 4 X 400 RELAY 1-2
22 1350 2050 MEN 4 X 100 RELAY FINAL
23 415 1115 WOMEN MARATHON FINAL
23 915 1615 WOMEN LONG JUMP FINAL
23 920 1620 MEN JAVELIN FINAL
23 925 1625 MEN 5000 FINAL
23 1000 1700 WOMEN 1500 FINAL
23 1025 1725 MEN 800 FINAL
23 1050 1750 WOMEN 4 X 400 RELAY FINAL
23 1115 1815 MEN 4 X 400 RELAY FINAL
Comment
Comment