CODEWITHSUNDEEP
excel
Fly_back
Fly_back

Reputation: 269

Remove seconds in the date-time number in Excel

I have a column of date-time numbers, "DD/MM/YY HH:MM:SS". At this moment, I want to remove "SS" from it and left "DD/MM/YY HH:MM". I tried DATE and TIME function separately but only one part left, DATE:"DD/MM/YY" and TIME:"HH:MM", also, I tried CONCATENATE function but it doesn't work, so could anyone do me a favour? Thanks in advance.

Upvotes: 3

Views: 52812

Answers (7)

Franklin Don Xavier
Franklin Don Xavier

Reputation: 9

to remove the seconds just do =INT("cell number"*1440)/1440

Upvotes: 0

Hamed
Hamed

Reputation: 71

Try this formula: 

Round(CELL * 60 * 24 , 0)/60/24

Upvotes: 1

A.N. Other
A.N. Other

Reputation: 1

I have a column showing dates "27/01/2015 18:43:00"

I changed them to show as Number "42031.78"

I then used Data/Text to columns and used the period or full stop as the delimiter.

This gave me two columns "42031.00" and "7798611111.00"

I deleted the second column and changed the original column back to short date format. "27/01/2015"

No more hours and seconds!

Upvotes: 0

Damian Fennelly
Damian Fennelly

Reputation: 91

If you want to completely remove the seconds from the data. Try: =1*TEXT(C2,"dd/mm/yyyy hh:mm")

Multiplying the text result by 1 converts the text back to a number.

Upvotes: 9

xinampc
xinampc

Reputation: 1788

You can try LEFT. Assuming I understand your question and you have DD/MM/YY HH:MM:SS in one cell, you can use =LEFT(cell,14)

Upvotes: 1

user4039065
user4039065

Reputation:

Select the cell(s) to be modified and tap Ctrl+1. Go to the Number tab and chose Custom from the list down the left. Supply the following for the Type:

dd/mm/yy hh:mm    ◄ EN-US
pp/kk/vv tt:mm    ◄ FI-FI

Click OK and you should be set.

    Ctrl+1 Custom Number Format

Upvotes: 2

nwhaught
nwhaught

Reputation: 1592

Try =TEXT(A1,"DD/MM/YY HH:MM")

Upvotes: 0

Related Questions