Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Claygate, Surrey, England
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Range Fill with date & time (Excel 2000)

    I am trying to plot the data from a series of temperature data loggers.

    The field I am trying to fill is in the format "5/27/03 13:42". I want to fill down the column, incrementing each cell by 5 minutes & 30 seconds (the period between each recorded temperature).

    Is there a simple way of doing this?

    Another data logger exports the date in one field and the time in HH:mm:ss in another. Is there any way to combine them in a column so the plot shows the date & time?

    Many thanks,

    Kevin

  2. #2
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range Fill with date & time (Excel 2000)

    Kevin,

    It is not too hard. I attached a workbook so you can see it, but the jist of it is:
    In the first cell type 5/27/03 13:42:00, press enter
    The the cell below it type 5/27/03 13:49:30 press enter

    Highlight the two cells and use the box in the bottom right corner of the selected range to copy the formulas down as far as you want.
    Hope this helps.

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Range Fill with date & time (Excel 2000)

    About your second question: you can simply add the date and time values: if A1 contains a date value and B1 a time value, the formula =A1+B1 combines them; Excel should even select the appropriate format, but you can also set it yourself.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range Fill with date & time (Excel 2000)

    Yes:

    Enter the starting value in the first cell. Right click on the fill handle in the lower right corner of the cell and drag down for as many cells as you want to fill. When you release the right button, select "Series..." (NOT "Fill Series") from the pop up menu. Click on Rows or Columns as appropriate, and in the Step box type 0:5:30. Click OK.

    As to your second question, it depends on how the date and time are entered into the worksheet. If they are entered into cells E1 and F1 as an Excel date value and time value, then the following formula will put the date and time into one cell:

    <pre>=E1+F1
    </pre>


    If they are entered into cells E1 and F1 as text strings, then you could use the following formula:

    <pre>=DATEVALUE(E1)+TIMEVALUE(F1)
    </pre>


    After converting, you can eliminate the formula by copying the cells containing the formula and doing a Paste Special and clicking on Values in the dialog box.
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •