Adding and subtracting time
Thanks David
"David Biddulph" wrote in message
...
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
"Terry Bennett" wrote in message
...
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(M30,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(Z30,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
|