LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
TSK TSK is offline
external usenet poster
 
Posts: 21
Default How to set the value to zero if it negative after the substract

Please advise

Q1: When applying the formula, D will show -38 but i need excel to change it
to zero and recongized it as number zero, otherwise when I multiple other
cell with this cell it will give value - Example 1.

Q2: However, I would also need cell D1 to reflect the number if it is not
zero or negative number - Example 2

Example 1

A1 - 01 Mar 2001 (Start Date)
B1 - 01 Mar 2006 (End Date)
C1 - 31 May 2008 (Audit Date)
D1 - (YEAR(B1)-YEAR(C1))*12+MONTH(B1)-MONTH(C1) = 0 instead of -38


Example 2

A1 - 01 Mar 2001 (Start Date)
B1 - 01 Jun 2010 (End Date)
C1 - 31 May 2008 (Audit Date)
D1 - I had this formula in cell D1:
(YEAR(B1)-YEAR(C1))*12+MONTH(B1)-MONTH(C1) = 25


 
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
How to substract for lunch on =MOD function jcc31 Excel Worksheet Functions 4 March 9th 06 07:30 PM
How do I substract numbers in range like this: Substract(a1..a10) Paulo New Users to Excel 3 November 17th 05 04:48 AM
how do I substract kiserlouisa New Users to Excel 1 May 24th 05 12:55 AM
Can you substract dates? Steph in PA Excel Worksheet Functions 5 May 23rd 05 08:23 PM
how do i substract in microsoft excel? nards Excel Worksheet Functions 2 February 6th 05 11:29 PM


All times are GMT +1. The time now is 06:56 AM.

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

About Us

"It's about Microsoft Excel"