+ Reply to Thread
Results 1 to 21 of 21

Extract Day name and Time rounded of from data time field

  1. #1
    Registered User
    Join Date
    12-06-2018
    Location
    Lancashire, England
    MS-Off Ver
    O365
    Posts
    12

    Extract Day name and Time rounded of from data time field

    Hi all,

    I was hoping somebody could help with a query i have.

    I have a date time field, from which i would like to extract the day of the week and the time (using MROUND) to round to nearest hour. It doesn't have to be MROUND if there is a simpler way to do this.

    Date is cell A1 looks like

    2019-01-02 11:43:21 YYMMDD HH:MM:SS
    by putting this formula in cell B1 i can extract the time =TIME(HOUR(A1),MINUTE(A1), SECOND(A1))
    by putting MROUND in C1 =MROUND(B1,1/24) i can get the time rounded off to the hour
    by putting in =CHOOSE(WEEKDAY(A1),"Sun","Mon","Tue","Wed","Thu","Fri","Sat") in D1 it selects the day of the week for me

    Is there a quicker or better way to get all this data and still retain the date format as i want to plot the results. Perhaps im wrong but converting to text doesnt order the Axis correctly when plotting.

    Any help will be greatly appreciated.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Extract Day name and Time rounded of from data time field

    =text(a1,"ddd")

    should do that for you. If not, please attach a sample excel sheet.

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Extract Day name and Time rounded of from data time field

    Try this to get the day

    =TEXT(DATEVALUE(LEFT(A1,10)), "ddd")

    and this to get the hour.

    =HOUR(TIMEVALUE(MID(A1,11,8)))

    Note, that will give you the actual hour, e.g. 11, from the time - not sure how you want to 'round' it.
    If posting code please use code tags, see here.

  4. #4
    Registered User
    Join Date
    12-06-2018
    Location
    Lancashire, England
    MS-Off Ver
    O365
    Posts
    12

    Re: Extract Day name and Time rounded of from data time field

    text ddd will only give me the day name. And i think when i plot it, it wont order the days in chronological order.

    attachment attached
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-06-2018
    Location
    Lancashire, England
    MS-Off Ver
    O365
    Posts
    12

    Re: Extract Day name and Time rounded of from data time field

    Thanks Norie

    I would like to see a time of 11:45AM to be replaced with 12:00PM, 10:32AM to be 11:00AM and so forth, basically rounding to the next hour.

    Is there a way to have your formula all in one cell to display the cell contents as eg Mon 7:00AM

  6. #6
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,482

    Re: Extract Day name and Time rounded of from data time field

    Weekday you can also find by reformatting your cell. if you reformat the cell to ddd or dddd
    you could even do that on column A with the original value. That will hide that date and time of column A

    In column B you can put 1 formula for the rounded time then.
    Since times are always a decimal (fraction of 1) you do not need the time formula to get only the time, substracting the whole number of date from the total number will also give you a decimal value of your time
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    just reformat B1 (if needed) to time format

    see also attachment example
    Attached Files Attached Files
    Last edited by Roel Jongman; 01-17-2019 at 06:05 AM.

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Extract Day name and Time rounded of from data time field

    OK. Try:

    =ROUND(24*A1,0)/24 and format as ddd hh:mm AM/PM
    Attached Files Attached Files

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Extract Day name and Time rounded of from data time field

    What would 11:15 round to?

  9. #9
    Registered User
    Join Date
    12-06-2018
    Location
    Lancashire, England
    MS-Off Ver
    O365
    Posts
    12

    Re: Extract Day name and Time rounded of from data time field

    ideally nearest hour, but either 11 or 12 will be fine.

  10. #10
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,482

    Re: Extract Day name and Time rounded of from data time field

    After looking at your file it can be further simplied

    in column B put formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    make a custom format ddd u:mm AM/PM for those cells and it will show you Wed 12:00PM

    Edit: missed the (almost same) reply by Glen Kennedy
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    12-06-2018
    Location
    Lancashire, England
    MS-Off Ver
    O365
    Posts
    12

    Re: Extract Day name and Time rounded of from data time field

    Sincere apologies, i need to keep the original cell format and contents as they are. This new formula will be in a different cell.

  12. #12
    Registered User
    Join Date
    12-06-2018
    Location
    Lancashire, England
    MS-Off Ver
    O365
    Posts
    12

    Re: Extract Day name and Time rounded of from data time field

    Quote Originally Posted by Roel Jongman View Post
    After looking at your file it can be further simplied

    in column B put formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    make a custom format ddd u:mm AM/PM for those cells and it will show you Wed 12:00PM

    Edit: missed the (almost same) reply by Glen Kennedy

    Thanks alot, slight hiccup sir, when i try and plot a graph with the new field, excel only drills down to the month, and not the granular detail such as Mon 7:00am
    Last edited by whistl; 01-17-2019 at 06:20 AM.

  13. #13
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,482

    Re: Extract Day name and Time rounded of from data time field

    Without seeing the graph that might a matter of resetting the scale of the axis.

    remember: dates are nothing more or less then decimal values all we do is put a formatting mask to appear as time

    the decimal value of your example dates are

    2019-01-02 11:43:21= 43.467,4884375
    2019-01-14 11:43:21= 43.479,4884375

    not sure graphs can easily reformat the axis value to show you may need to put a datatable (option for some graphs) under the x-axle to see the "wed 12:00) values

    We will need another example with graph to help solve this if you cannot find out yourself

  14. #14
    Registered User
    Join Date
    12-06-2018
    Location
    Lancashire, England
    MS-Off Ver
    O365
    Posts
    12

    Re: Extract Day name and Time rounded of from data time field

    I have attached the updated formula file, i have also made a tweak to the way the day will be displayed by splitting days and times, that way they become easier to plot.

    The issue is when i do the plot based on date, the format doesnt carry into the table even though i have set the format in the pivot to match the field format.
    Attached Files Attached Files

  15. #15
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Extract Day name and Time rounded of from data time field

    Can you post a sample of the data you want to chart?

  16. #16
    Registered User
    Join Date
    12-06-2018
    Location
    Lancashire, England
    MS-Off Ver
    O365
    Posts
    12

    Re: Extract Day name and Time rounded of from data time field

    posted above sir in the formula updated file

  17. #17
    Registered User
    Join Date
    12-06-2018
    Location
    Lancashire, England
    MS-Off Ver
    O365
    Posts
    12

    Re: Extract Day name and Time rounded of from data time field

    Quote Originally Posted by Roel Jongman View Post
    Weekday you can also find by reformatting your cell. if you reformat the cell to ddd or dddd
    you could even do that on column A with the original value. That will hide that date and time of column A

    In column B you can put 1 formula for the rounded time then.
    Since times are always a decimal (fraction of 1) you do not need the time formula to get only the time, substracting the whole number of date from the total number will also give you a decimal value of your time
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    just reformat B1 (if needed) to time format

    see also attachment example
    This almost works, the only issue being that when you have a time either side off 00:00, it rounds both up at 00:00, thats not so much of an issue and i can live with that. The problem comes when you create a pivot, the Pivot differentiates both values and one appears fine as 00:00 but the other appears as 01/01/1900.

    Does anybody have any thoughts how to get round this?
    Last edited by whistl; 01-17-2019 at 09:50 AM. Reason: Not worked

  18. #18
    Registered User
    Join Date
    12-06-2018
    Location
    Lancashire, England
    MS-Off Ver
    O365
    Posts
    12

    Re: Extract Day name and Time rounded of from data time field

    Quote Originally Posted by whistl View Post
    This almost works, the only issue being that when you have a time either side off 00:00, it rounds both up at 00:00, thats not so much of an issue and i can live with that. The problem comes when you create a pivot, the Pivot differentiates both values and one appears fine as 00:00 but the other appears as 01/01/1900.

    Does anybody have any thoughts how to get round this?
    Updated - Solution almost works. Does anybody have any ideas how i can get passed the 00:00 and 01/01/1900 issue?

  19. #19
    Registered User
    Join Date
    12-06-2018
    Location
    Lancashire, England
    MS-Off Ver
    O365
    Posts
    12

    Re: Extract Day name and Time rounded of from data time field

    =ROUNDDOWN(A1*24,0)/24-INT(A1)

    This rounds to the first component of HH:MM (Hour)

    its to plot a time series so it'll work for me and it removes the 00:00 and 01/01/1900 issue.

  20. #20
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,482

    Re: Extract Day name and Time rounded of from data time field

    Quote Originally Posted by whistl View Post
    I have attached the updated formula file, i have also made a tweak to the way the day will be displayed by splitting days and times, that way they become easier to plot.

    The issue is when i do the plot based on date, the format doesnt carry into the table even though i have set the format in the pivot to match the field format.

    Ah, ok, I see what went "wrong"

    1. Like I expected the pivottable wizard is set to automaticly group date values. can be very annoying.. Microsoft calls is smart grouping
    after I changed the formula and made a new pivot in your example you see the times. But if you have an existing pivot you want to keep then you might need to upgroup the column to get the original values instead of grouped values
    check here info on grouping and ungrouping
    https://support.office.com/en-us/art...c-c84a5a340725

    2. You need the times regardless of date. also makes sense why you need weekdays in separate column then..

    I changed the raw data column T_opened so it will show only time and disregard the datepart.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Now it will show 12:00PM for any date in the same row


    Your other problem with the days is because of the choose function it sees them as text and sorts automaticly. On filter fields it is hard to sort in the logic weekday sequence.
    But normally those values could be sorted based on custom sortlist. I think it is not possible to custom sort on filter field.
    have a read of this page to understand sorting using custom lists
    https://support.office.com/en-us/art...b-70050aa22523

    it might work to drag the day column into a row or column field, then apply customsort to it and then move it back to the filter section.
    Attached Files Attached Files
    Last edited by Roel Jongman; 01-17-2019 at 11:36 AM. Reason: Missed the ppage 2 replys of the topic so added quote of post I replied too

  21. #21
    Registered User
    Join Date
    12-06-2018
    Location
    Lancashire, England
    MS-Off Ver
    O365
    Posts
    12

    Re: Extract Day name and Time rounded of from data time field

    Thanks Roel, you are a legend sir

+ 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. [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
  2. How can I extract an undated time value from a date/time field?
    By NPB in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-06-2013, 08:47 AM
  3. [SOLVED] Extracted time from date/time field does not flag on =IF(TIME function.
    By Vlad717 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-09-2013, 03:29 PM
  4. [SOLVED] Help taking a time from one field and outputting a time period in a different column
    By Hidden_Gecko in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-07-2013, 12:42 AM
  5. Replies: 4
    Last Post: 03-23-2010, 12:44 PM
  6. Userform with Time Field displays Time as Number
    By randolphoralph in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-29-2008, 10:36 AM
  7. Using rounded time with vlookup
    By Zug in forum Excel General
    Replies: 3
    Last Post: 04-29-2006, 09:10 PM
  8. Remove time from a date and time field? Format removes the displa.
    By oaoboc in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-16-2005, 03:35 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