Announcement

Collapse
No announcement yet.

Any EXCEL gurus out there?

Collapse

Unconfigured Ad Widget

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Any EXCEL gurus out there?

    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.

  • #2
    Dates and times can be added and subtracted in Excel but there are a few tricks. Simple reference here.

    If you are running Vista, you can also have multiple time clocks on display. Reference

    Comment


    • #3
      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


      • #4
        Originally posted by bambam
        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.
        Thanks bambam. I got what I wanted. Not familiar with the PASTE SPECIAL deal so that is what probably messed me up. After I CUT/PASTED the new values into column B and deleted the "700" column, column B then changed to "#....!" forget the word that came after the pound sign. Did not know how to handle this so I just hid the column that contained "700". It's probably in the PAST SPECIAL step that I went wrong. Anyway, this is my finished product. Thanks again. Hmmm, in the preview of this msg, my columns got squeezed together. Oh well.

        "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

        Working...
        X