+ Reply to Thread
Results 1 to 12 of 12

Function to extract hour from non standard DateTime

  1. 02-05-2018, 05:03 PM #1
    webwyzard
    webwyzard is offline
    Forum Contributor
    Join Date
    02-03-2018
    Location
    Buckinghamshire, England
    MS-Off Ver
    2010
    Posts
    130

    Function to extract hour from non standard DateTime

    Hi

    I have looked for solution on the forum but am running into trouble trying to find a function to allow me to extract the hour from a datetime formatted as below

    2016.08.15 21:38:41

    I would like the function to return a number not a format of this cell that merely displays a number if that makes sense.

    I want to use the resultant cell to test for the hour of day and use it as selection criteria in a chart.

    I could be over complicating the issue. My strengths are in Access VBA etc and excel is not well known to me.

    Any help would be appreciated.

    Neil
    Reply With QuoteRegister To Reply
  2. 02-05-2018, 05:11 PM #2
    Mazzaropi
    Mazzaropi is offline
    Valued Forum Contributor
    Join Date
    05-13-2010
    Location
    Belo Horizonte, Brazil
    MS-Off Ver
    Excel 2003; 2007
    Posts
    441

    Re: Function to extract hour from non standard DateTime

    webwyzard, Good evening.

    I'm supposing your data is in a text format at this moment.

    Suppose:

    A1 --> 2016.08.15 21:38:41


    Format B1 as DATE --> hh:mm:ss

    B1 --> =--MID(A1, SEARCH(" ",A1)+1, 8)


    Is that what you're looking for?
    I hope it helps.
    ...If my answer helped you, Please, click on. * Add Reputation (at left)

    Best regards.
    Marc?lio Lob?o
    Reply With QuoteRegister To Reply
  3. 02-05-2018, 05:16 PM #3
    Aardigspook
    Aardigspook is offline
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Function to extract hour from non standard DateTime

    Welcome to the forum.

    If it's an actual date+time, then use this:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    (where the date+time is in A2).

    If instead it's text representing the date+time, then use this:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    What I mean by 'actual date+time' is that Excel treats dates and times just as numbers - if you re-format a 'date' cell as 'General' or 'Number' format, you'll get something like 42597.9019 (for your example). If you reformat the cell and nothing changes, then it's just numbers representing date/time.

    Hope that makes sense and helps.
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.
    Reply With QuoteRegister To Reply
  4. 02-05-2018, 05:16 PM #4
    mjr veverka
    mjr veverka is offline
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,154

    Re: Function to extract hour from non standard DateTime

    "HOUR" ("MINUTE", "SECOND") do not work ?
    Reply With QuoteRegister To Reply
  5. 02-05-2018, 05:19 PM #5
    Jonmo1
    Jonmo1 is offline
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Function to extract hour from non standard DateTime

    The first thing we need to know is if the value you have is an actual date/time value, or just a TEXT string.

    Test with
    =ISNUMBER(A1)
    Where A1 is the cell containing your date/time.
    Is it True or False ?

    If it's TRUE, you can just use
    =HOUR(A1)

    If it's FALSE, try
    =HOUR(REPLACE(A1,1,FIND(" ",A1),""))
    Last edited by Jonmo1; 02-05-2018 at 05:21 PM.
    Reply With QuoteRegister To Reply
  6. 02-05-2018, 05:27 PM #6
    webwyzard
    webwyzard is offline
    Forum Contributor
    Join Date
    02-03-2018
    Location
    Buckinghamshire, England
    MS-Off Ver
    2010
    Posts
    130

    Re: Function to extract hour from non standard DateTime

    Hi

    The mid function worked. The cell contents did not change with a change in format.

    I did try replacing the "." with "-" and formatting as date and then using the hour function but it involved an extra step and i was not sure the resultnt cell would then act as a number. I suspect it probably would.

    Thanks for your very rapid responses all.
    Reply With QuoteRegister To Reply
  7. 02-05-2018, 05:31 PM #7
    Mazzaropi
    Mazzaropi is offline
    Valued Forum Contributor
    Join Date
    05-13-2010
    Location
    Belo Horizonte, Brazil
    MS-Off Ver
    Excel 2003; 2007
    Posts
    441

    Re: Function to extract hour from non standard DateTime

    webwyzard,

    Did you try the suggestion at response #2 ?

    https://www.excelforum.com/excel-for...ml#post4836770
    Reply With QuoteRegister To Reply
  8. 02-05-2018, 05:36 PM #8
    Aardigspook
    Aardigspook is offline
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Function to extract hour from non standard DateTime

    You're welcome, glad we could help.
    As you're new to the forum, you may not be aware that you can acknowledge those who helped you by giving reputation - click on the * Add Reputation link below the post or posts which helped you.
    Thank you for the feedback and for marking the thread as Solved.
    Reply With QuoteRegister To Reply
  9. 02-06-2018, 08:16 AM #9
    webwyzard
    webwyzard is offline
    Forum Contributor
    Join Date
    02-03-2018
    Location
    Buckinghamshire, England
    MS-Off Ver
    2010
    Posts
    130

    Re: Function to extract hour from non standard DateTime

    This is handy as i have had situations before where cheking this would have helped.
    Reply With QuoteRegister To Reply
  10. 02-06-2018, 08:17 AM #10
    webwyzard
    webwyzard is offline
    Forum Contributor
    Join Date
    02-03-2018
    Location
    Buckinghamshire, England
    MS-Off Ver
    2010
    Posts
    130

    Re: Function to extract hour from non standard DateTime

    Quote Originally Posted by Mazzaropi View Post
    webwyzard,

    Did you try the suggestion at response #2 ?
    I did but it gave 01:26:45 as a result.
    Last edited by AliGW; 02-06-2018 at 08:20 AM. Reason: Quotation tags amended.
    Reply With QuoteRegister To Reply
  11. 02-06-2018, 08:20 AM #11
    webwyzard
    webwyzard is offline
    Forum Contributor
    Join Date
    02-03-2018
    Location
    Buckinghamshire, England
    MS-Off Ver
    2010
    Posts
    130

    Re: Function to extract hour from non standard DateTime

    Quote Originally Posted by Jonmo1 View Post
    The first thing we need to know is if the value you have is an actual date/time value, or just a TEXT string.

    =HOUR(REPLACE(A1,1,FIND(" ",A1),""))
    I tried this and it also gave a correct result
    Last edited by AliGW; 02-06-2018 at 08:21 AM. Reason: Quotation tags amended.
    Reply With QuoteRegister To Reply
  12. 02-06-2018, 08:22 AM #12
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2405 (Windows 11 23H2 64-bit)
    Posts
    81,417

    Re: Function to extract hour from non standard DateTime

    Webwyzard - you are adding opening quotation tags withouth the matching closing ones!!! I have amended both instances.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Count If Start Datetime and End Datetime is less than 30 seconds
    By crappygeezer in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-23-2017, 07:48 AM
  2. if statement for datetime value between two other datetime values
    By OSepulvedaIII in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-15-2016, 10:37 AM
  3. COUNTIF Function using time to extract call stats per hour
    By MHayward in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-10-2014, 08:45 AM
  4. How to use the wait function to create a program that tells time every hour on the hour
    By PANTECH in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-07-2014, 11:26 AM
  5. Based on a Column datetime value auto calculate and populate a datetime range
    By rajashanmuga in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-22-2010, 04:10 PM
  6. [SOLVED] How to I convert standard time to Military or 24 hour format?
    By Nacho in forum Excel General
    Replies: 5
    Last Post: 06-28-2006, 02:20 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1