Link to home
Start Free TrialLog in
Avatar of Eduardo Fuerte
Eduardo FuerteFlag for Brazil

asked on

Could you point a way the obtain the total line filtered at an Excel sheet?

Hi Experts

Could you point a way to mantain only the hh:mm:ss on the Excel cells?

Accordingly to:
User generated imageSo,to eliminate yyyy-mm-dd parts, maybe by applying a formula.

Thanks in advance.
Avatar of Bembi
Flag of Germany image

You can just format the cell as date with the pattern hh:mm:ss,
Or you use a format function with the same pattern.
Ifr it is text and not recognized by a date pattern, the teh option may be to use a macro to seach for the Space and the dot.
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
with a formula for text:
=MID(D2;FIND(" ";D2);9)
Avatar of Eduardo Fuerte


Thank you!
If they are are date and time values, a simpler formula would be:


Dates are held in Excel as a serial number of number of days since 01 Jan 1900 (1904 on some systems just to complicate matters) with 1 Jan 1900 being day 1, today (25 May 2021) is day 44341.

Time is stored as a decimal portion of a day, 06:00 being 0.25, 12:00 being 0.5, 18:00 being 0.75 as a few simple examples with nice round decimals.

Using your first entry as an example, 16/05/2021 22:15:18.0, this converts to serial number  44,332.93 ie 44332 whole days and 0.93 part day.

=A2-INT(A2) would take just the integer part of the value, the whole days, and this would be deducted from the complete value leaving just the part days, ie the time.

You could also use Text to columns to separate the date and time.

Highlight the list of values and select the Text to Columns option in the Data menu.
Step 1) - Choose Delimited, click Next
Step 2) - Deselect all of the delimiter options and select Space, the preview pane at the bottom will show the values split into date and time, click Next
Step 3) - If you want to keep the dates then select the Date column in the preview section, select the Date radio button and choose the relevant format (YMD), this is the format of the source data not what format is required later. If you don't want to keep the dates, select the "Do not import" option. If you want the data to stay where it is the Destination will already show the first cell of the selected range; if you want the data moved, use the range browser to select a cell; using this option will not overwrite the original data. Click Finish.
Thank you for these extra solutions!