Convert date with time into time as of 12:00:00 AM

**jeffreybrown** · 09-23-2011, 11:51 AM

Curios if somebody could share a smarter way to accomplish this?

This formula works for taking col A and converting it to col B with the time always converting to 12:00:00 AM, but the date stays the same.

=TEXT(INT(A1),"mmm d, yyyy")&" "&TEXT(SUBSTITUTE(MOD(A1,1),MOD(A1,1),0),"hh:mm:ss AM/PM")

May 26, 2010 11:02:54 AM >> May 26, 2010 12:00:00 AM
Mar 5, 2011 1:46:09 PM >> Mar 5, 2011 12:00:00 AM

Is there a better way?

**Richard Buttrey** · 09-23-2011, 12:15 PM

Hi,

Why not just format the A1 cell directly with the custom format

mmm dd, yyyy "12:00:00 AM"

Regards

**jeffreybrown** · 09-23-2011, 12:26 PM

Hi Richard,

This works for formatting, but I needed the time actually changed to 12:00:00 AM because of a further calculation.

Example: 6/11/2010 2:07:58 PM - 12/14/2009 12:00:00 AM equals elapsed days 179.589

I need the elapsed days to be 179, but not just by display. It should be a whole number.

**Richard Buttrey** · 09-23-2011, 12:46 PM

Hi,

=INT(A1) perhaps?

Regards

**jeffreybrown** · 09-23-2011, 12:57 PM

Hi Richard,

Thanks, yes, I think I overlooked the obvious again...

This work fine =INT(B1)-INT(A1)

**mike donnell** · 10-28-2011, 09:49 AM

I am trying to calculate and display the elapsed time based on a formula using the distance in one cell divided by the rate in another cell. for example:

A1 (distance) = 2 miles
B1 (speed) = 100 miles per hour
C1 (time) should = and display 00:001:12 (or 1 minute 12 seconds)

The formula distance/(mph/60) returns 1.2 minutes which is accurate but I need it to display the minutes and seconds if possible.

**jeffreybrown** · 10-28-2011, 06:54 PM

Try...

Divide the results by 1440 and apply a custom format >> m:ss

Next time, could you please follow the forum rules and keep seperate post in their own thread. This helps to facilitate the search capability when people are looking for the answer to your type of question.