+ Reply to Thread
Results 1 to 9 of 9

Read time data in a column and calculate number of each time specifiation

  1. #1
    Registered User
    Join Date
    10-08-2017
    Location
    Strasbourg, France
    MS-Off Ver
    Excel 2016
    Posts
    9

    Read time data in a column and calculate number of each time specifiation

    Hi there,

    I have a column with several different time specifications. Now I want to create a formula, that evaluates every single time specification in this column, group it afterwards and calculate the number of each time specification. Is there I chance how I can fix this problem?

    Thanks you for your help
    Sflany

  2. #2
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,666

    Re: Read time data in a column and calculate number of each time specifiation

    .
    So ... for example if you had 20 entries in Col A (A1:A20) consisting of time entries ... you want the formula to return the number 20 say in B1 ?

    If this is your goal look at the excel formula for COUNTA

    http://https://support.office.com/en...2-53f3219e2509

  3. #3
    Registered User
    Join Date
    10-08-2017
    Location
    Strasbourg, France
    MS-Off Ver
    Excel 2016
    Posts
    9

    Re: Read time data in a column and calculate number of each time specifiation

    thank you for your answer, but that is not what I want to do

    I have a column with different times for example 12:00:00, 09:00:00, 12:15:00 and so on (see image).

    example.JPG

    In a first step I would like to find out every single time information given in this column. In a second step I would like to calculate the number of each time.

  4. #4
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,666

    Re: Read time data in a column and calculate number of each time specifiation

    .
    Perhaps it's just me but your request is still confusing.


    Please explain in different words what this means :

    find out every single time information given in this column

    Does this :

    like to calculate the number of each time.
    mean you want to know how many of the time entries are (say) 12 noon ? And how many might be 5 pm, etc ?

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,826

    Re: Read time data in a column and calculate number of each time specifiation

    Attach a sample workbook (not image).

    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 then scroll down to Manage Attachments to open the upload window.

  6. #6
    Registered User
    Join Date
    10-08-2017
    Location
    Strasbourg, France
    MS-Off Ver
    Excel 2016
    Posts
    9

    Re: Read time data in a column and calculate number of each time specifiation

    Attached you can find my data:

    I try to explain what I want to do.

    In the yellow column you can find all data I have. In a next step I would like to get a list where there are only these times listed which can be found in the yellow column. In my example data (column D) I listed every single minute of a day, but I just want to know the times listed in column A.
    Then I would like to calculate how often every single time can be found (see therefor the green column / which should be the final result).
    Attached Files Attached Files
    Last edited by sflany; 10-30-2017 at 10:29 AM.

  7. #7
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,582

    Re: Read time data in a column and calculate number of each time specifiation

    My first thought was to use a pivot table. If you are unfamiliar with Excel's pivot tables, this should get you started: http://www.excel-easy.com/data-analy...ot-tables.html I chose to use time_data as the row labels and count of time_data as the values in the pivot table.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  8. #8
    Registered User
    Join Date
    10-08-2017
    Location
    Strasbourg, France
    MS-Off Ver
    Excel 2016
    Posts
    9

    Re: Read time data in a column and calculate number of each time specifiation

    Thanks for your help MrShorty - it worked perfectly with a pivot table!

  9. #9
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,538

    Re: Read time data in a column and calculate number of each time specifiation

    It seems you'd like to create an unique distinct list of times in column A in D column, then count.
    I will try to create a A-Z order list. Try as follow:

    In D2:
    =MIN($A$2:$A$825)

    D3:
    Please Login or Register  to view this content.
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Drag down till you get "0:00:00"

+ 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. Replies: 7
    Last Post: 04-25-2020, 03:23 AM
  2. VBA calculate sleep time, when time is entered in military time format
    By axm1955 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-29-2017, 10:28 AM
  3. Replies: 12
    Last Post: 02-12-2017, 06:09 PM
  4. [SOLVED] auto read column list every time form opens
    By k1dr0ck in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-23-2016, 10:08 PM
  5. [SOLVED] Calculate number of hours from Start date and time and End date and time
    By sathyasun in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-28-2013, 01:04 AM
  6. Replies: 0
    Last Post: 04-12-2013, 01:27 AM
  7. sapi to read time in normal format not as serial number
    By megabytecompute in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-26-2008, 03:03 PM

Tags for this Thread

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