Hi I have a column "A1" to "A300" with time data (06:19:21), how can I
subtract a half hour? The seconds should not be relevant in the final column.

Thank you
--
smile

On Mon, 10 Aug 2009 13:34:01 -0700, israel
wrote:

Hi I have a column "A1" to "A300" with time data (06:19:21), how can I
subtract a half hour? The seconds should not be relevant in the final column.

Thank you

Try the following formula:

=FLOOR(G17-1/48,1/1440)

Hope this helps / Lars-Åke

On Mon, 10 Aug 2009 21:03:43 GMT, Lars-Åke Aspelin
wrote:

On Mon, 10 Aug 2009 13:34:01 -0700, israel
wrote:

Hi I have a column "A1" to "A300" with time data (06:19:21), how can I
subtract a half hour? The seconds should not be relevant in the final column.

Thank you

Try the following formula:

=FLOOR(G17-1/48,1/1440)

Hope this helps / Lars-Åke

=FLOOR(A1-1/48,1/1440)

in cell B1

Copy down to cell B300

Lars-Åke

If your times are true Excel times and you want to drop the seconds:

A1 = 6:19:21

=TIME(HOUR(A1),MINUTE(A1),0)-TIME(0,30,0)

Format as h:mm

--
Biff
Microsoft Excel MVP


"israel" wrote in message
...
Hi I have a column "A1" to "A300" with time data (06:19:21), how can I
subtract a half hour? The seconds should not be relevant in the final
column.

Thank you
--
smile

Beautiful, it worked like a baby, to be honest I can't make sense of your
formula but it worked.

While I'm at it, there is one more step I forgot to mention. Once I have
this sollution, (you have enlighted me) now I have to add a column that will
show time added ten minutes.

column "B" minus 30 minutes which I have already
column "C" should be added 10 minutes to column "B"

Thank you
--
smile


"Lars-Ã…ke Aspelin" wrote:

On Mon, 10 Aug 2009 21:03:43 GMT, Lars-Ã…ke Aspelin
wrote:

On Mon, 10 Aug 2009 13:34:01 -0700, israel
wrote:

Hi I have a column "A1" to "A300" with time data (06:19:21), how can I
subtract a half hour? The seconds should not be relevant in the final column.

Thank you

Try the following formula:

=FLOOR(G17-1/48,1/1440)

Hope this helps / Lars-Ã…ke

=FLOOR(A1-1/48,1/1440)

in cell B1

Copy down to cell B300

Lars-Ã…ke

On Mon, 10 Aug 2009 17:11:24 -0400, "T. Valko"
wrote:

If your times are true Excel times and you want to drop the seconds:

A1 = 6:19:21

=TIME(HOUR(A1),MINUTE(A1),0)-TIME(0,30,0)

Format as h:mm

This looks nicer than my proposal.
It can be a bit more compact, like this:

=TIME(HOUR(A1)+24,MINUTE(A1)-30,0)

The +24 is needed to be able to handle input before 00:30 AM.

Hope this helps / Lars-Åke

On Mon, 10 Aug 2009 21:06:41 GMT, Lars-Åke Aspelin
wrote:

On Mon, 10 Aug 2009 21:03:43 GMT, Lars-Åke Aspelin
wrote:

On Mon, 10 Aug 2009 13:34:01 -0700, israel
wrote:

Hi I have a column "A1" to "A300" with time data (06:19:21), how can I
subtract a half hour? The seconds should not be relevant in the final column.

Thank you

Try the following formula:

=FLOOR(G17-1/48,1/1440)

Hope this helps / Lars-Åke

=FLOOR(A1-1/48,1/1440)

in cell B1

Copy down to cell B300

Lars-Åke


In order to be able to handle times before 00:30 you have to revise
the formula a bit, like this:

=FLOOR(A1+1-1/48,1/1440)

But I suggest you go for the type of formula suggested in another
proposal based on the TIME function that is more easy to understand.

Lars-Åke

On Mon, 10 Aug 2009 14:23:01 -0700, israel
wrote:

Beautiful, it worked like a baby, to be honest I can't make sense of your
formula but it worked.

While I'm at it, there is one more step I forgot to mention. Once I have
this sollution, (you have enlighted me) now I have to add a column that will
show time added ten minutes.

column "B" minus 30 minutes which I have already
column "C" should be added 10 minutes to column "B"

Thank you


In C1 try this:

=FLOOR(B1+1/144,1/1440)

or, more easy to understand, go for the TIME function suggested by
Biff, like this:

=TIME(HOUR(B1),MINUTE(B1)+10,0)

Hope this helps / Lars-Åke

before 00:30 AM.

I didn't even think about that!

--
Biff
Microsoft Excel MVP


"Lars-Åke Aspelin" wrote in message
...
On Mon, 10 Aug 2009 17:11:24 -0400, "T. Valko"
wrote:

If your times are true Excel times and you want to drop the seconds:

A1 = 6:19:21

=TIME(HOUR(A1),MINUTE(A1),0)-TIME(0,30,0)

Format as h:mm

This looks nicer than my proposal.
It can be a bit more compact, like this:

=TIME(HOUR(A1)+24,MINUTE(A1)-30,0)

The +24 is needed to be able to handle input before 00:30 AM.

Hope this helps / Lars-Åke

Hi,

It was some time since you replied to my inquiry and it certainly was very
helpful.

This time around I am required to subtract 31 minutes from the original time.
Since I did not understand the formula I am in a dilema how to work it
around to add the minute.

Just so I am on track with you people, I have column A-1 thru 300 data that
indicates time "6:23:34" now I have to subtract 31 minutes (seconds
irrevelant)

Please reply
Thank you
--
smile


"Lars-Ã…ke Aspelin" wrote:

On Mon, 10 Aug 2009 13:34:01 -0700, israel
wrote:

Hi I have a column "A1" to "A300" with time data (06:19:21), how can I
subtract a half hour? The seconds should not be relevant in the final column.

Thank you

Try the following formula:

=FLOOR(G17-1/48,1/1440)

Hope this helps / Lars-Ã…ke