Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding and subtracting dates | Excel Worksheet Functions | |||
ADDING/SUBTRACTING FROM RIGHT TO LEFT | Excel Worksheet Functions | |||
Adding/Subtracting Time Help... | Excel Worksheet Functions | |||
adding/subtracting time | Excel Worksheet Functions | |||
Adding Subtracting Time Formula-Horse Racing | Excel Discussion (Misc queries) |