Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 67
Default Adding and subtracting time

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


  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 620
Default Adding and subtracting time

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



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 67
Default 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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding and subtracting dates Bjair Excel Worksheet Functions 3 August 9th 06 05:25 AM
ADDING/SUBTRACTING FROM RIGHT TO LEFT Joanne Excel Worksheet Functions 6 July 28th 06 07:25 PM
Adding/Subtracting Time Help... killertofu Excel Worksheet Functions 3 February 15th 06 09:49 PM
adding/subtracting time [email protected] Excel Worksheet Functions 5 January 11th 06 03:43 PM
Adding Subtracting Time Formula-Horse Racing SMDIYDLI Excel Discussion (Misc queries) 1 December 13th 04 10:27 PM


All times are GMT +1. The time now is 02:51 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"