+ Reply to Thread
Results 1 to 8 of 8

creating a formula for time and date reordering

  1. #1
    Registered User
    Join Date
    09-21-2015
    Location
    London
    MS-Off Ver
    2013
    Posts
    5

    Smile creating a formula for time and date reordering

    hi,
    I have a long list that shows a date (col' A) the hour (col' B) and a metric that I have chosen (col' C) .
    something like this:

    date (A) hour (B) metric (C)
    01/01/2015 01:00 55
    01/01/2015 02:00 66
    01/01/2015 03:00 77
    and so on...

    I've want to change the order of the data >> instead of getting a line for each date i will get a col' for each date (and the hours will be the rows- corresponding with their metrics)...
    something like this:

    col' A col' B col B' col' C
    01/01/2015 02/01/2015 03/01/2015 04/01/2015 ... .... ....
    00:00 metric metric metric metric
    01:00 metric metric metric metric
    02:00 metric metric metric metric
    03:00 metric metric metric metric
    04:00 metric metric metric metric
    ...
    ...
    ...
    ...

    help(?)

    cheers!
    Last edited by Meiuz; 09-21-2015 at 05:50 AM.

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,683

    Re: creating a formula for time and date reordering

    I would use a pivot table to change the layout

    see example
    Attached Files Attached Files
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: creating a formula for time and date reordering

    pls attach sample excel file
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  4. #4
    Registered User
    Join Date
    09-21-2015
    Location
    London
    MS-Off Ver
    2013
    Posts
    5

    Red face Re: creating a formula for time and date reordering

    hi,
    added an excel to try and specify my needs(see diferrent tabs)

    thank you!
    Attached Files Attached Files

  5. #5
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: creating a formula for time and date reordering

    A pivot table seems to be the way to go:
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  6. #6
    Registered User
    Join Date
    09-21-2015
    Location
    London
    MS-Off Ver
    2013
    Posts
    5

    Re: creating a formula for time and date reordering

    i don't know why but the hours became a long repetitive list (not showing only 24 hours.. it keeps repeating itself)..
    do you know why it happens? (sometimes i miss the smallest things and it's annoying )

    thank you for your patiance

  7. #7
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: creating a formula for time and date reordering

    Did you set up your pivot table exactly as I did?

    Can you upload your workbook?
    Directions:
    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

  8. #8
    Registered User
    Join Date
    09-21-2015
    Location
    London
    MS-Off Ver
    2013
    Posts
    5

    Re: creating a formula for time and date reordering

    ha!!!! got it... something went "ga-ga" with the excel sheet but i fixed it..

    thank you for your help!
    M

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Need assistance creating a formula to show the difference between two date/time
    By livebythecoad in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-15-2014, 11:20 PM
  2. Replies: 3
    Last Post: 12-19-2013, 06:49 AM
  3. Replies: 1
    Last Post: 08-21-2013, 09:26 AM
  4. Run-time error when reordering items in tables (ListObject)
    By Cihan Chen in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 07-08-2013, 09:45 AM
  5. [SOLVED] Creating Time Field and a Date+Time Column
    By marshymell0 in forum Excel General
    Replies: 12
    Last Post: 06-14-2013, 11:30 AM
  6. creating a date and time formula.
    By Merlin46 in forum Excel General
    Replies: 15
    Last Post: 09-13-2011, 04:53 PM
  7. [SOLVED] Creating a Combined Date/Time Shortcut
    By Michael Link in forum Excel General
    Replies: 7
    Last Post: 02-14-2005, 09:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1