formula please: if a cell is blank then don't subtract dates

A

aneer miss

New Member
Joined
Nov 28, 2010
Messages
13
hi

I would like to subtract 2 dates in two different cells, but only if both cells contain dates.

In the eg below I would like to subtract JB8 from JB7 and put the result in IZ7..then subtract JB9 from JB8 and put result in IZ8 etc.



new


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 62px"><COL style="WIDTH: 62px"><COL style="WIDTH: 96px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>IZ</TD><TD>JA</TD><TD>JB</TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD></TD><TD></TD><TD style="FONT-FAMILY: Arial Unicode MS; FONT-WEIGHT: bold">DATE</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>#NAME?</TD><TD style="TEXT-ALIGN: right; COLOR: #008000">8</TD><TD style="TEXT-ALIGN: right; COLOR: #008000">17/04/11</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD></TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">12/03/11</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD></TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">2/03/11</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD></TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">16/02/11</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD></TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">12/01/11</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD></TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">27/12/10</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD></TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">28/11/10</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD></TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">1</TD><TD style="TEXT-ALIGN: right">14/11/10</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD></TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">1</TD><TD style="TEXT-ALIGN: right">6/06/10</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>IZ7</TD><TD>=IF(isblank (JB8)=TRUE,"",JB7-JB8)</TD></TR><TR><TD>JA7</TD><TD>=A7</TD></TR><TR><TD>JB7</TD><TD>=B7</TD></TR><TR><TD>JA8</TD><TD>=A8</TD></TR><TR><TD>JB8</TD><TD>=B8</TD></TR><TR><TD>JA9</TD><TD>=A9</TD></TR><TR><TD>JB9</TD><TD>=B9</TD></TR><TR><TD>JA10</TD><TD>=A10</TD></TR><TR><TD>JB10</TD><TD>=B10</TD></TR><TR><TD>JA11</TD><TD>=A11</TD></TR><TR><TD>JB11</TD><TD>=B11</TD></TR><TR><TD>JA12</TD><TD>=A12</TD></TR><TR><TD>JB12</TD><TD>=B12</TD></TR><TR><TD>JA13</TD><TD>=A13</TD></TR><TR><TD>JB13</TD><TD>=B13</TD></TR><TR><TD>JA14</TD><TD>=A14</TD></TR><TR><TD>JB14</TD><TD>=B14</TD></TR><TR><TD>JA15</TD><TD>=A15</TD></TR><TR><TD>JB15</TD><TD>=B15</TD></TR><TR><TD>JA16</TD><TD>=A16</TD></TR><TR><TD>JB16</TD><TD>=B16</TD></TR><TR><TD>JA17</TD><TD>=A17</TD></TR><TR><TD>JB17</TD><TD>=B17</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

The problem arises with IZ15 as I don't want a value in the cell because JB16 is blank.

The end result should ideally be a zero in IZ15, not 40335 as shown below:

new

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 62px"><COL style="WIDTH: 62px"><COL style="WIDTH: 62px"><COL style="WIDTH: 96px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>IY</TD><TD>IZ</TD><TD>JA</TD><TD>JB</TD></TR><TR style="HEIGHT: 23px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD> </TD><TD> </TD><TD> </TD><TD style="FONT-FAMILY: Arial Unicode MS; FONT-WEIGHT: bold">DATE</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD> </TD><TD style="TEXT-ALIGN: right">36</TD><TD style="TEXT-ALIGN: right; COLOR: #008000">8</TD><TD style="TEXT-ALIGN: right; COLOR: #008000">17/04/11</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD> </TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">12/03/11</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD> </TD><TD style="TEXT-ALIGN: right">14</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">2/03/11</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD> </TD><TD style="TEXT-ALIGN: right">35</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">16/02/11</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD> </TD><TD style="TEXT-ALIGN: right">16</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">12/01/11</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD> </TD><TD style="TEXT-ALIGN: right">29</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">27/12/10</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD> </TD><TD style="TEXT-ALIGN: right">14</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">28/11/10</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD> </TD><TD style="TEXT-ALIGN: right">161</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">1</TD><TD style="TEXT-ALIGN: right">14/11/10</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD> </TD><TD style="TEXT-ALIGN: right">40335</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">1</TD><TD style="TEXT-ALIGN: right">6/06/10</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right"> </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>IZ7</TD><TD>=JB7-JB8</TD></TR><TR><TD>JA7</TD><TD>=A7</TD></TR><TR><TD>JB7</TD><TD>=B7</TD></TR><TR><TD>IZ8</TD><TD>=JB8-JB9</TD></TR><TR><TD>JA8</TD><TD>=A8</TD></TR><TR><TD>JB8</TD><TD>=B8</TD></TR><TR><TD>IZ9</TD><TD>=JB9-JB10</TD></TR><TR><TD>JA9</TD><TD>=A9</TD></TR><TR><TD>JB9</TD><TD>=B9</TD></TR><TR><TD>IZ10</TD><TD>=JB10-JB11</TD></TR><TR><TD>JA10</TD><TD>=A10</TD></TR><TR><TD>JB10</TD><TD>=B10</TD></TR><TR><TD>IZ11</TD><TD>=JB11-JB12</TD></TR><TR><TD>JA11</TD><TD>=A11</TD></TR><TR><TD>JB11</TD><TD>=B11</TD></TR><TR><TD>IZ12</TD><TD>=JB12-JB13</TD></TR><TR><TD>JA12</TD><TD>=A12</TD></TR><TR><TD>JB12</TD><TD>=B12</TD></TR><TR><TD>IZ13</TD><TD>=JB13-JB14</TD></TR><TR><TD>JA13</TD><TD>=A13</TD></TR><TR><TD>JB13</TD><TD>=B13</TD></TR><TR><TD>IZ14</TD><TD>=JB14-JB15</TD></TR><TR><TD>JA14</TD><TD>=A14</TD></TR><TR><TD>JB14</TD><TD>=B14</TD></TR><TR><TD>IZ15</TD><TD>=JB15-JB16</TD></TR><TR><TD>JA15</TD><TD>=A15</TD></TR><TR><TD>JB15</TD><TD>=B15</TD></TR><TR><TD>JA16</TD><TD>=A16</TD></TR><TR><TD>JB16</TD><TD>=B16</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Could someone please help with a formula?

I have had a try:

=IF(isblank (JB8)=TRUE,"",JB7-JB8)

but it doesn't work (error:#name?)

Thanks in advance.
Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
Hi

Remove the space after isblank.
<table valign="middle" colspan="2" style="font-family: Calibri; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-size: 11px; font-weight: normal; font-style: normal;" border="1" cellpadding="2" cellspacing="0"><colgroup><col width="28pt"><col width="60pt"></colgroup><tbody><tr style="background-color: rgb(250, 250, 250);"><td colspan="2" align="middle">Worksheet 'new'</td></tr><tr style="background-color: rgb(202, 202, 202);"><td>
</td><td align="middle">IZ</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">7</td><td align="right">36</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">8</td><td align="right">10</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">9</td><td align="right">14</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">10</td><td align="right">35</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">11</td><td align="right">16</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">12</td><td align="right">29</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">13</td><td align="right">14</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">14</td><td align="right">161</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">15</td><td align="left">
</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">16</td><td align="left">
</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">17</td><td align="left">
</td></tr></tbody></table>
<table valign="middle" colspan="2" style="table-layout: auto; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-family: Calibri; font-size: 10px;" border="3" cellpadding="2" cellspacing="0"><colgroup><col width="40pt"><col></colgroup><tbody><tr style="background-color: rgb(238, 170, 170);"><td>Cell</td><td>Formula</td></tr><tr><td>IZ7</td><td>=IF(ISBLANK(JB8)=TRUE,"",JB7-JB8)</td></tr></tbody></table><table style="font-family: Arial; font-size: 7pt;"><tbody><tr><td style="color: rgb(51, 51, 51);">Created with Tab2HTML (v2.4.1). ©Gerd alias Bamberg</td></tr></tbody></table>
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
622
smozgur
smozgur
D
  • Question Question
Looking for a way to allocate shifts between teams, maybe a LET formula
Replies
0
Views
89
Dazzybeeguy
D
E
  • Question Question
VBA code clean up
Replies
9
Views
580
mumps
mumps
E
  • Question Question
Adding a custom message.
Replies
1
Views
663
Micron
M
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C

Forum statistics

Threads
1,224,566
Messages
6,179,553
Members
452,928
Latest member
101blockchains

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top