CODEWITHSUNDEEP
excelexcel-formula
Cathy C.
Cathy C.

Reputation: 11

Convert date & time in Excel, to time only, and round to nearest 30 minutes

I need a formula to convert excel date & time to time only and nearest 30 minutes. Example 1/5/2017 4:38:29 PM convert to 1630

          A1                    B1
 1/5/2017  4:38:29 PM          1630
 1/5/2017  5:03:40 PM          1700
 1/5/2017  4:39:27 PM          1630

I tried if function & vlookup with 

=MROUND((TEXT(A1,"hhmm")),1/48)

but I get 0:00 answer, and also

=MOD(Y5,"0:30")

but I get a 0 answer.

Upvotes: 1

Views: 1152

Answers (2)

pnuts
pnuts

Reputation: 59450

Assuming data starts in A1, copy the following down to suit:

=HOUR(A1)&IF(MINUTE(A1)>29,30,"00")

Upvotes: 0

user4039065
user4039065

Reputation:

It looks like you were almost there. You need MOD to get the decimal portion of a datetime (i.e. the time) then MROUND to the nearest half-hour.

=MROUND(MOD(A1, 1),TIME(0,30,0))

enter image description here

FLOOR and CEILING operate the same as MROUND in case you require the lower or higher half-hour.

You may need to format the cell for the desired time format mask.

Upvotes: 2

Related Questions