Subtracting Decimal Time (4.3 minutes) from time value 10:00 am

[susanlbaker]

I have the following values that are exported out of Epic EMR (electronic medical record).

The first column is a decimal time value, for example the first example is 4.3 minutes.
I want to subtract the 4.3 minutes from the time stamp of 10:01 am in the second column.

I realize I could manually do this, but there are 134,000 records in this file.

Any help would be greatly appreciated.


[TABLE="width: 183"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]SignIn To ChkIn[/TD]
[TD]C/I Time[/TD]
[/TR]
[TR]
[TD="align: right"]4.3[/TD]
[TD="align: right"]10:01 AM[/TD]
[/TR]
[TR]
[TD="align: right"]3.6[/TD]
[TD="align: right"]10:02 AM[/TD]
[/TR]
[TR]
[TD="align: right"]15.6[/TD]
[TD="align: right"]10:02 AM[/TD]
[/TR]
[TR]
[TD="align: right"]4.4[/TD]
[TD="align: right"]10:03 AM[/TD]
[/TR]
</tbody>[/TABLE]

 

[nils7]

Hi Susan,

Excel stores time in decimal form but displays it as time format. 1 day thereby equals 1.0

So 1 hour is1/24 = 0.04 and 1 minute is 0.04/60.

So all you need to do is to write a formula like this:

=H1-(G1*0.04/60)

With H1 being the start time and G1 being the adjustment in minutes. The output might be shown as a decimal figure. Just format it as "time", and you get the desired result.

Cheers,

Nils

 

[susanlbaker]

Thank you so much!!! I would have never figured that out and your explanation was perfect!! Thanks again!

Susan

 

[joeu2004]

So 1 hour is 1/24 = 0.04 and 1 minute is 0.04/60. So all you need to do is to write a formula like this:

=H1-(G1*0.04/60)

1/24 is not exactly 0.04. So I would not use that approximation. Instead, I would simply write:

=H1 - G1/1440

where H1 is an Excel date and/or time, and G1 is time in decimal minutes.

This works because there are 1440 minutes in a day, and Excel stores time as a fraction of a day.