• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 45
  • Last Modified:

How to extract date from date and time format on excel. eg. 08/04/2017 08.59 PM in the format (dd/mm/yyyy) UK format to date only in another cell

I want to extract date only from a system given date and time stamp on excel.


08/04/2017 08.59 PM = 08/04/2017

The above is in dd/mm/yyyy format. need help.
arunav ghosh
arunav ghosh
  • 2
1 Solution
Is source cell text or date field?
Sample workbook will be helpful
If it is text, you can try:
=DATE(MID(A1,7,4), MID(A1,4,2), LEFT(A1,2))
arunav ghoshInformation processing specialistAuthor Commented:
Source is Date field, but your solution worked,  Thank you :)
arunav ghoshInformation processing specialistAuthor Commented:
Great solution on dates so far after 3 days of google search on UK and US dates conversation. :)
Rob HensonFinance AnalystCommented:
If that solution worked, then the field has to be text. If it were a true date then the MID function would not have worked.

Dates and time are held in excel as a serial number.

The date is number of days since the start of Excel's internal calendar, day 1 is 01 January 1900 so today's date (18 Dec 2017) would be 43087. Doing a MID function on that as per the above would give a number of 707943 which when formatted as a date shows 12 April 3838.

Time is then stored as a fraction of a day, eg 6:00am is 0.25 ie 1/4 through the day, 12:00 midday is 0.5 ie halfway through the day.

For a field which is truly a date, the following would work:


The INT function takes the integer of the value. Based on what I mention above the Integer part of a serial number representing a date and time is just the date element.

Likewise, to strip just the time from a date and time value you can use:

=MOD(A1,1)   the MOD function takes the remainder of a value when divided by the factor specified. Therefore dividing a value by 1 leaves just the decimal part of the value which in a date and time value is just the time element.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now