Adding and subtracting time

[Terry Bennett]

I have a spreadsheet where I am calculating changes to opening hours in a
number of outlets.

Columns C to N detail existing hours (ie; Monday Open, Monday Close, etc)
and then Column O is the total hours per week for each outlet. The function
I am using in this column is:

=D3-C3+F3-E3+H3-G3+J3-I3+L3-K3+(IF(M3>0,N3-M3,0))

The IF function seems to be necessary as not every branch is open on a
Saturday and with no data in columns M and N, I receive error messages.

Columns Q to AA detail proposed hours in the same format and Column AB is
then the total of these:

=Q3-P3+S3-R3+U3-T3+W3-V3+Y3-X3+(IF(Z3>0,AA3-Z3,0))

The problem I have is that in Column AC I need to show the difference
between existing and proposed hours in the format: +h:mm or -h:mm. Using a
simple

=AB3 - O3 doesn't work - it gives multiple ########### signs in the result
cell.

I have formated columns O and AB as: [h]:mm and column AC as:
+[h]:mm;-[h]:mm;-

Any suggestions would be appreciated!

Many thanks.

Terry

 

[David Biddulph]

Tools/ Options/ Calculation/ 1904 date system will allow negative values.
But if you do that, be careful of transferring dates to & fro between that
sheet & others using the usual 1900 system.

 

[Terry Bennett]

Thanks David

Tools/ Options/ Calculation/ 1904 date system will allow negative values.
But if you do that, be careful of transferring dates to & fro between that
sheet & others using the usual 1900 system.
--
David Biddulph

I have a spreadsheet where I am calculating changes to opening hours in a
number of outlets.

Columns C to N detail existing hours (ie; Monday Open, Monday Close, etc)
and then Column O is the total hours per week for each outlet. The
function I am using in this column is:

=D3-C3+F3-E3+H3-G3+J3-I3+L3-K3+(IF(M3>0,N3-M3,0))

The IF function seems to be necessary as not every branch is open on a
Saturday and with no data in columns M and N, I receive error messages.

Columns Q to AA detail proposed hours in the same format and Column AB is
then the total of these:

=Q3-P3+S3-R3+U3-T3+W3-V3+Y3-X3+(IF(Z3>0,AA3-Z3,0))

The problem I have is that in Column AC I need to show the difference
between existing and proposed hours in the format: +h:mm or -h:mm. Using
a simple

=AB3 - O3 doesn't work - it gives multiple ########### signs in the
result cell.

I have formated columns O and AB as: [h]:mm and column AC as:
+[h]:mm;-[h]:mm;-

Any suggestions would be appreciated!

Many thanks.

Terry