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

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.

Eg.

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

The above is in dd/mm/yyyy format. need help.
0
arunav ghosh
• 2
1 Solution

Commented:
Is source cell text or date field?
If it is text, you can try:
=DATE(MID(A1,7,4), MID(A1,4,2), LEFT(A1,2))
1

Information processing specialistAuthor Commented:
Source is Date field, but your solution worked,  Thank you :)
0

Information processing specialistAuthor Commented:
Great solution on dates so far after 3 days of google search on UK and US dates conversation. :)
0

Finance 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:

=INT(A1)

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.
0
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.