Working with hours

[msoutlookenator]

Hi,

I have two columns of hours in [h]:mm format. Column A1 is reading
148:00 and column B1 is reading 175:00.

I have a formula that says B1-A1 giving me the correct answer 27:00.

In the next row, I have the same formula but with the hours 148:00 in
A2 and 140:00 in B2. I would like the answer to read -8:00. Is this
possible? When I try it, I get a load of hashes. I've tried changing
the formatting but nothing seems to work.



Hope you can help,
Thanks
H

 

[Peo Sjoblom]

You can't use negative time values in Excel unless you use the Mac's
datesystem under tools>options>calculation and 1904 date system

If you change beware that all current dates will get 1462 days added to them

 

[Scoops]

Hi,

I have two columns of hours in [h]:mm format. Column A1 is reading
148:00 and column B1 is reading 175:00.

I have a formula that says B1-A1 giving me the correct answer 27:00.

In the next row, I have the same formula but with the hours 148:00 in
A2 and 140:00 in B2. I would like the answer to read -8:00. Is this
possible? When I try it, I get a load of hashes. I've tried changing
the formatting but nothing seems to work.

Hope you can help,
Thanks
H

Hi H

Tools > Options > Calculation tab, check "1904 date system"

Note that this will shift any dates already entered in the workbook by
1462 days - enter 1462 in a cell, copy it, select the altered dates
and PasteSpecial Values and Subtract

Regards

Steve

 

[msoutlookenator]

Hi,

I have two columns of hours in [h]:mm format. Column A1 is reading
148:00 and column B1 is reading 175:00.

I have a formula that says B1-A1 giving me the correct answer 27:00.

In the next row, I have the same formula but with the hours 148:00 in
A2 and 140:00 in B2. I would like the answer to read -8:00. Is this
possible? When I try it, I get a load of hashes. I've tried changing
the formatting but nothing seems to work.

Hope you can help,
Thanks
H

Hi H

Tools > Options > Calculation tab, check "1904 date system"

Note that this will shift any dates already entered in the workbook by
1462 days - enter 1462 in a cell, copy it, select the altered dates
and PasteSpecial Values and Subtract

Regards

Steve

Thanks. Will this change in the options only affect the current
workbook?

 

[Scoops]

On 18 May, 22:22, (e-mail address removed) wrote:

Hi,
I have two columns of hours in [h]:mm format. Column A1 is reading
148:00 and column B1 is reading 175:00.
I have a formula that says B1-A1 giving me the correct answer 27:00.
In the next row, I have the same formula but with the hours 148:00 in
A2 and 140:00 in B2. I would like the answer to read -8:00. Is this
possible? When I try it, I get a load of hashes. I've tried changing
the formatting but nothing seems to work.
Hope you can help,
Thanks
H

Hi H

Tools > Options > Calculation tab, check "1904 date system"

Note that this will shift any dates already entered in the workbook by
1462 days - enter 1462 in a cell, copy it, select the altered dates
and PasteSpecial Values and Subtract

Steve

Thanks. Will this change in the options only affect the current
workbook?- Hide quoted text -

- Show quoted text -

Yes, only the active workbook is affected.

Regards

Steve