Results 1 to 4 of 4

Thread: MS Access remove date but keep time
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default MS Access remove date but keep time

    Hello,

    I have a date/time field that either contains the date and time "1/1/1900 5:30:00 PM" or just the time "6:30:00 PM". I would like to remove the date if it's there and only keep the time when I run my query. Can someone help?

    Thanks,
    Adam

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    49,669
    Post Thanks / Like
    Mentioned
    51 Post(s)
    Tagged
    11 Thread(s)

    Default Re: MS Access remove date but keep time

    Welcome to the Board!

    You may be able to just use the TIMEVALUE function.

    It is important to understand how Access and Excel track date and time. Basically, they store them as numbers, specifically the number of days since 1/0/1900.
    So, the days are just integers, and the time is a fraction (the fractional amount of one day). So, you can also get just the time by subtracting the Integer component of the date, i.e.
    [MyDate]-Int([MyDate])
    and just format the result as time.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    Apr 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MS Access remove date but keep time

    Thanks Joe for the quick response! Can you give the full formula example including the format results as time? I'm new at this but I'm learning.
    Thanks!

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    49,669
    Post Thanks / Like
    Mentioned
    51 Post(s)
    Tagged
    11 Thread(s)

    Default Re: MS Access remove date but keep time

    Have you tried just adding a calculated field to your query that uses the TIMEVALUE function to see if it returns what you want, i.e.
    Code:
    CalcField: DateValue([YourDateTimeField])
    I am assuming that you know how to create calculated fields in a query. If you do not, just do a Google search on "creating calculated fields in an Access query", and you will finds lots of example, videos, and tutorials.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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