+ Reply to Thread
Results 1 to 13 of 13

DATE AND TIME from mm/dd/yyThhmm to date time HELP!!!!!

  1. #1
    Registered User
    Join Date
    12-20-2014
    Location
    chicago
    MS-Off Ver
    Office 2013
    Posts
    10

    Question DATE AND TIME from mm/dd/yyThhmm to date time HELP!!!!!

    I have many rows of data and several columns (maybe 10) of dates/ times written in a cell , also in military time, with this format

    9/10/13T2300
    10/1/14T215
    etc
    etc

    Essentially I need to find the difference in time between two columns. For example:

    Date/time A - Date/time B = amount of time

    this could be days, hours, minutes, or etc

    but i CAN'T FORMAT THE DATE/TIMES!!!!!
    Attached Files Attached Files
    Last edited by kaczynski; 12-20-2014 at 03:27 AM. Reason: sheet

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: DATE AND TIME from mm/dd/yyThhmm to date time HELP!!!!!

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. 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 demonstrated, mock them up manually if needed. Remember to desensitize the data.

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

    View Pic


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: DATE AND TIME from mm/dd/yyThhmm to date time HELP!!!!!

    Hi, welcome to the forum

    If your data does look exactly like you showed...
    9/10/13T2300
    10/1/14T215
    Then this is not proper time, it is text looking like time - and formatting it will not change that.

    This, in a helper column, will pull out the date...
    =DATEVALUE(LEFT(A1,SEARCH("t",A1,1)-1))

    But I will need to see more samples of the time, to put something together for that. You said it was military time, but I dont think it is. If it was, then 215 would be 0215
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    12-20-2014
    Location
    chicago
    MS-Off Ver
    Office 2013
    Posts
    10

    Re: DATE AND TIME from mm/dd/yyThhmm to date time HELP!!!!!

    hey I attached a workbook, let me know if it is clear! thank you!!

  5. #5
    Registered User
    Join Date
    12-20-2014
    Location
    chicago
    MS-Off Ver
    Office 2013
    Posts
    10

    Re: DATE AND TIME from mm/dd/yyThhmm to date time HELP!!!!!

    Hey you're right, maybe format is not the right word, but the time is military it just isn't in the right format. which causes some of the problem because some need that extra zero 0910 and not 910. I attached a workbook, let me know if this is clear, thank you!

  6. #6
    Registered User
    Join Date
    12-20-2014
    Location
    chicago
    MS-Off Ver
    Office 2013
    Posts
    10

    Re: DATE AND TIME from mm/dd/yyThhmm to date time HELP!!!!!

    Hey you're formula is helpful! but when i use it, the time gets funky. meaning the date shows but then the time reverts to 0:00 and not 00:45 .. i formatted it to date.. ah so difficult

  7. #7
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: DATE AND TIME from mm/dd/yyThhmm to date time HELP!!!!!

    Try this formula

    =LEFT(B4,FIND("T",B4)-1)+REPLACE(RIGHT(B4,4),3,,":")

    and format cells Custom, m/d/yyyy h:mm
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  8. #8
    Registered User
    Join Date
    12-20-2014
    Location
    chicago
    MS-Off Ver
    Office 2013
    Posts
    10

    Re: DATE AND TIME from mm/dd/yyThhmm to date time HELP!!!!!

    holy cow i think it worked!!!!! thanks every1!!

  9. #9
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: DATE AND TIME from mm/dd/yyThhmm to date time HELP!!!!!

    Also for the Days, Hours and Minutes you can use this formula

    =(K4-H4)

    and format cells Custom, d "days" h "hours" m "minutes"

    Row\Col
    H
    I
    J
    K
    L
    2
    Date/Time of A Date/Time of B Amount of Time from A to B (=B-A)
    3
    Ideally would be:
    4
    12/21/2010 20:31
    12/21/2010 21:13
    0 days 0 hours 42 minutes
    5
    7/1/2010 22:01
    7/2/2010 3:04
    0 days 5 hours 3 minutes
    6
    6/17/2010 7:18
    6/17/2010 7:18
    0 days 0 hours 0 minutes
    7
    7/6/2011 18:22
    7/8/2011 11:30
    1 days 17 hours 8 minutes
    8
    7/16/2011 16:30
    7/16/2011 17:12
    0 days 0 hours 42 minutes
    9
    11/28/2011 17:09
    11/28/2011 18:00
    0 days 0 hours 51 minutes
    10
    10/9/2011 23:48
    10/15/2011 22:55
    5 days 23 hours 7 minutes
    11
    9/19/2011 15:05
    9/19/2011 16:08
    0 days 1 hours 3 minutes
    12
    1/7/2012 3:45
    1/7/2012 15:57
    0 days 12 hours 12 minutes
    13
    1/14/2012 4:50
    1/14/2012 12:57
    0 days 8 hours 7 minutes
    14
    7/7/2012 21:44
    7/7/2012 22:55
    0 days 1 hours 11 minutes
    15
    6/11/2012 22:15
    6/11/2012 22:33
    0 days 0 hours 18 minutes
    16
    9/10/2013 23:00
    9/10/2013 23:25
    0 days 0 hours 25 minutes

  10. #10
    Registered User
    Join Date
    12-20-2014
    Location
    chicago
    MS-Off Ver
    Office 2013
    Posts
    10

    Re: DATE AND TIME from mm/dd/yyThhmm to date time HELP!!!!!

    wow SO impressed!!! wonder if you can help me with another challenge

  11. #11
    Registered User
    Join Date
    12-20-2014
    Location
    chicago
    MS-Off Ver
    Office 2013
    Posts
    10

    Re: DATE AND TIME from mm/dd/yyThhmm to date time HELP!!!!!

    what is the best way to organize the spreadsheet if a column can have a few options of values or text for each row. to be able to count the end statistics easily.

    Example:

    column A
    row 1 a
    row 2 b
    row 3 a and b
    row 4 empty


    How many rows have a; b; a and b?

  12. #12
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: DATE AND TIME from mm/dd/yyThhmm to date time HELP!!!!!

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. 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 demonstrated, mock them up manually if needed. Remember to desensitize the data.

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

    View Pic

  13. #13
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: DATE AND TIME from mm/dd/yyThhmm to date time HELP!!!!!

    Maybe this one

    Enter your criteria in B1, C1 and D1

    Enter formula in B2 and pull it to the right

    =COUNTIF($A$1:$A$4,"*"&B$1&"*")

    Row\Col
    A
    B
    C
    D
    1
    A
    A
    B
    AB
    2
    B
    2
    2
    1
    3
    AB

+ 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: 3
    Last Post: 12-19-2013, 06:49 AM
  2. Calculation of time difference between the Start date & time & End Date & time
    By Harry Jones in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 12-17-2012, 03:54 PM
  3. Replies: 0
    Last Post: 07-17-2012, 10:11 AM
  4. Calculating days & time left from start date/time to end date/time
    By marie in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-07-2005, 10:40 AM
  5. Replies: 0
    Last Post: 08-23-2005, 12:24 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