MS Access remove date but keep time

prwbblue

New Member
Joined
Apr 17, 2019
Messages
2
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
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,042
Office Version
365
Platform
Windows
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.
 

prwbblue

New Member
Joined
Apr 17, 2019
Messages
2
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!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,042
Office Version
365
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,709
Messages
5,446,091
Members
405,379
Latest member
EDGOUG

This Week's Hot Topics

Top