Time in mm.ss over 1440??

[Guest]

Hi There

OK, this will probably seem simple to the MVPs out there, but I'm stumped!

I have established a spreadsheet as follows: (I did it like this so my data entry folks wouldn't have to type in the ":" for time calculations

Col A: minutes entered in with a format of Numbe
Col B: seconds entered in with a format of Numbe
Col C: Total time which is the concatenation of a1 and b1 with the TIME function ie. TIME(,a1,b1). THe format is [mm].ss so that I don't role over to hour

I then Sum the C column with a format of [mm].ss

For example,
Col A Col B Col
792 4 792.0
18 33 18.3
433 53 433.5
776 56 776.5
TOTAL 2021.2

It works like a charm except when the value in the A Column is greater than 1440. It then just displays the amount over the 1440 correctly. For example 1515 displays as 75 minutes. Naturally then, my TOTAL from Column C is incorrect.

Any suggestions on how I can get this to work correctly??
I appreciate any suggestions!

Thanks so much
Ingrid

 

[Bernie Deitrick]

Ingrid,

Use the formula

=TIME(0,A1,B1)+INT(A1/1440)

HTH,
Bernie
MS Excel MVP

Hi There!

OK, this will probably seem simple to the MVPs out there, but I'm stumped!

I have established a spreadsheet as follows: (I did it like this so my

data entry folks wouldn't have to type in the ":" for time calculations)

Col A: minutes entered in with a format of Number
Col B: seconds entered in with a format of Number
Col C: Total time which is the concatenation of a1 and b1 with the TIME

function ie. TIME(,a1,b1). THe format is [mm].ss so that I don't role over
to hours

I then Sum the C column with a format of [mm].ss

For example,
Col A Col B Col C
792 4 792.04
18 33 18.33
433 53 433.53
776 56 776.56
TOTAL 2021.26

It works like a charm except when the value in the A Column is greater

than 1440. It then just displays the amount over the 1440 correctly. For
example 1515 displays as 75 minutes. Naturally then, my TOTAL from Column C
is incorrect.

 

[Harald Staff]

Hi Ingrid

Forget TIME, it removes dates (and 1440 minutes is a day). Try
=A1/1440+B1/86400

HTH. Best wishes Harald

Hi There!

OK, this will probably seem simple to the MVPs out there, but I'm stumped!

I have established a spreadsheet as follows: (I did it like this so my

data entry folks wouldn't have to type in the ":" for time calculations)

Col A: minutes entered in with a format of Number
Col B: seconds entered in with a format of Number
Col C: Total time which is the concatenation of a1 and b1 with the TIME

function ie. TIME(,a1,b1). THe format is [mm].ss so that I don't role over
to hours

I then Sum the C column with a format of [mm].ss

For example,
Col A Col B Col C
792 4 792.04
18 33 18.33
433 53 433.53
776 56 776.56
TOTAL 2021.26

It works like a charm except when the value in the A Column is greater

than 1440. It then just displays the amount over the 1440 correctly. For
example 1515 displays as 75 minutes. Naturally then, my TOTAL from Column C
is incorrect.

 

[Peo Sjoblom]

Use it like this instead

=A1/1440+B1/86400

format as [mm]:ss

--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

Hi There!

OK, this will probably seem simple to the MVPs out there, but I'm stumped!

I have established a spreadsheet as follows: (I did it like this so my

data entry folks wouldn't have to type in the ":" for time calculations)

Col A: minutes entered in with a format of Number
Col B: seconds entered in with a format of Number
Col C: Total time which is the concatenation of a1 and b1 with the TIME

function ie. TIME(,a1,b1). THe format is [mm].ss so that I don't role over
to hours

I then Sum the C column with a format of [mm].ss

For example,
Col A Col B Col C
792 4 792.04
18 33 18.33
433 53 433.53
776 56 776.56
TOTAL 2021.26

It works like a charm except when the value in the A Column is greater

than 1440. It then just displays the amount over the 1440 correctly. For
example 1515 displays as 75 minutes. Naturally then, my TOTAL from Column C
is incorrect.

 

[Ken Wright]

Hi Ingrid, nothing to add to the replies you already have, but would just advise
against targeting questions to MVPs, even if only by inference, as this is a big
community with plenty of non-MVPs ready and able to help with practically any
question you can come up with.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

Hi There!

OK, this will probably seem simple to the MVPs out there, but I'm stumped!

I have established a spreadsheet as follows: (I did it like this so my data

entry folks wouldn't have to type in the ":" for time calculations)

Col A: minutes entered in with a format of Number
Col B: seconds entered in with a format of Number
Col C: Total time which is the concatenation of a1 and b1 with the TIME

function ie. TIME(,a1,b1). THe format is [mm].ss so that I don't role over to
hours

I then Sum the C column with a format of [mm].ss

For example,
Col A Col B Col C
792 4 792.04
18 33 18.33
433 53 433.53
776 56 776.56
TOTAL 2021.26

It works like a charm except when the value in the A Column is greater than

1440. It then just displays the amount over the 1440 correctly. For example
1515 displays as 75 minutes. Naturally then, my TOTAL from Column C is
incorrect.