Date formula confusion

J

jerry.rozario

New Member
Joined
Dec 22, 2010
Messages
19
Office Version
  1. 365
Platform
  1. Windows
Hello,
First of all thanks for your previous help. This time i have another trouble found in my date formula. Please advice.

<TABLE class=MsoNormalTable style="MARGIN: auto auto auto 4.65pt; WIDTH: 350pt; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0in 5.4pt 0in 5.4pt" cellSpacing=0 cellPadding=0 width=467 border=0><TBODY><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; BACKGROUND: #c5d9f1; PADDING-BOTTOM: 0in; BORDER-LEFT: #f0f0f0; WIDTH: 70pt; PADDING-TOP: 0in; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt" noWrap width=93>
A<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; BACKGROUND: #c5d9f1; PADDING-BOTTOM: 0in; BORDER-LEFT: #f0f0f0; WIDTH: 70pt; PADDING-TOP: 0in; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt" noWrap width=93>
A<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; BACKGROUND: #f2dddc; PADDING-BOTTOM: 0in; BORDER-LEFT: #f0f0f0; WIDTH: 70pt; PADDING-TOP: 0in; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt" width=93>
B<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; BACKGROUND: #f2dddc; PADDING-BOTTOM: 0in; BORDER-LEFT: #f0f0f0; WIDTH: 70pt; PADDING-TOP: 0in; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt" width=93>
B<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: white 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; BACKGROUND: white; PADDING-BOTTOM: 0in; BORDER-LEFT: #f0f0f0; WIDTH: 70pt; PADDING-TOP: 0in; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; mso-border-right-alt: solid white .5pt" width=93>
A/B<o:p></o:p>

​
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 1"><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; BACKGROUND: #c5d9f1; PADDING-BOTTOM: 0in; BORDER-LEFT: #f0f0f0; WIDTH: 70pt; PADDING-TOP: 0in; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt" noWrap width=93>
Date Done<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; BACKGROUND: #c5d9f1; PADDING-BOTTOM: 0in; BORDER-LEFT: #f0f0f0; WIDTH: 70pt; PADDING-TOP: 0in; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt" noWrap width=93>
Next Date<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; BACKGROUND: #f2dddc; PADDING-BOTTOM: 0in; BORDER-LEFT: #f0f0f0; WIDTH: 70pt; PADDING-TOP: 0in; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt" width=93>
Date Done<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: #f0f0f0; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; BACKGROUND: #f2dddc; PADDING-BOTTOM: 0in; BORDER-LEFT: #f0f0f0; WIDTH: 70pt; PADDING-TOP: 0in; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt" width=93>
Next Date<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: white 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; BACKGROUND: white; PADDING-BOTTOM: 0in; BORDER-LEFT: #f0f0f0; WIDTH: 70pt; PADDING-TOP: 0in; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; mso-border-right-alt: solid white .5pt" width=93>
Date<o:p></o:p>

​
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 2; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: #9bbb59 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #9bbb59 1pt solid; PADDING-LEFT: 5.4pt; BACKGROUND: #eaf1dd; PADDING-BOTTOM: 0in; BORDER-LEFT: #9bbb59 1pt solid; WIDTH: 70pt; PADDING-TOP: 0in; BORDER-BOTTOM: #9bbb59 1pt solid; HEIGHT: 12.75pt; mso-border-alt: solid #9BBB59 .5pt" noWrap width=93>
4/13/2011<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: #9bbb59 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #9bbb59 1pt solid; PADDING-LEFT: 5.4pt; BACKGROUND: #eaf1dd; PADDING-BOTTOM: 0in; BORDER-LEFT: #f0f0f0; WIDTH: 70pt; PADDING-TOP: 0in; BORDER-BOTTOM: #9bbb59 1pt solid; HEIGHT: 12.75pt; mso-border-right-alt: solid #9BBB59 .5pt; mso-border-top-alt: solid #9BBB59 .5pt; mso-border-bottom-alt: solid #9BBB59 .5pt" noWrap width=93>
4/12/2012<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: #9bbb59 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #9bbb59 1pt solid; PADDING-LEFT: 5.4pt; BACKGROUND: #eaf1dd; PADDING-BOTTOM: 0in; BORDER-LEFT: #f0f0f0; WIDTH: 70pt; PADDING-TOP: 0in; BORDER-BOTTOM: #9bbb59 1pt solid; HEIGHT: 12.75pt; mso-border-right-alt: solid #9BBB59 .5pt; mso-border-top-alt: solid #9BBB59 .5pt; mso-border-bottom-alt: solid #9BBB59 .5pt" noWrap width=93>
4/13/2011<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: #9bbb59 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #9bbb59 1pt solid; PADDING-LEFT: 5.4pt; BACKGROUND: #eaf1dd; PADDING-BOTTOM: 0in; BORDER-LEFT: #f0f0f0; WIDTH: 70pt; PADDING-TOP: 0in; BORDER-BOTTOM: #9bbb59 1pt solid; HEIGHT: 12.75pt; mso-border-alt: solid #9BBB59 .5pt; mso-border-left-alt: solid #9BBB59 .5pt" noWrap width=93>
4/13/2011<o:p></o:p>​


</TD><TD style="BORDER-RIGHT: #9bbb59 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; PADDING-LEFT: 5.4pt; BACKGROUND: #eaf1dd; PADDING-BOTTOM: 0in; BORDER-LEFT: #f0f0f0; WIDTH: 70pt; PADDING-TOP: 0in; BORDER-BOTTOM: #9bbb59 1pt solid; HEIGHT: 12.75pt; mso-border-right-alt: solid #9BBB59 .5pt; mso-border-bottom-alt: solid #9BBB59 .5pt" noWrap width=93></TD></TR></TBODY></TABLE>​
<o:p></o:p>
In A/B date means either (A next date) nor (B next date) will be deduct 40 days in A/B date. My point is in (A next date) or (B next date) whichever is appear the A/B date column shows the result means (result-40days). N.B. A or B any one will be entered not both at a time.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
thank you both formula's are work well.
If some one entry both date at the same time, is it possible to show an "ERROR" in that column where the result supposed to be.
 
Upvote 0
Thanks all are works well except when i add a formula in B next date column (c5+365) it shows error. is it possible to skip that option. Because i have to use that formula in the b5.

a b c d e
<TABLE style="WIDTH: 249pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=331 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2413" width=66><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" width=73><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 30pt; BACKGROUND-COLOR: #ccccff" width=64 height=40 rowSpan=2>A</TD><TD class=xl71 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 50pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #ccccff" width=66 rowSpan=2>A</TD><TD class=xl72 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #ff99cc" width=64 rowSpan=2>B</TD><TD class=xl72 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 55pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #ff99cc" width=73 rowSpan=2>B</TD><TD class=xl74 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 rowSpan=2>A/B</TD></TR><TR style="HEIGHT: 15pt" height=20></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 30pt; BACKGROUND-COLOR: #ccccff" width=64 height=40 rowSpan=2>Date Done</TD><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 50pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #ccccff" width=66 rowSpan=2>Next Date</TD><TD class=xl69 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #ff99cc" width=64 rowSpan=2>Date Done</TD><TD class=xl69 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 55pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #ff99cc" width=73 rowSpan=2>Next Date</TD><TD class=xl76 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 rowSpan=2>Date</TD></TR><TR style="HEIGHT: 15pt" height=20></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl73 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: #3867a6 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #ffffcc" width=64 height=21>4/13/2011</TD><TD class=xl73 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 50pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: #ffffcc" width=66>4/12/2012</TD><TD class=xl73 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: #ffffcc" width=64> </TD><TD class=xl78 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 55pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: #ffffcc" align=right width=73>12/30/1900</TD><TD class=xl77 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: #ffffcc" width=64>error</TD></TR></TBODY></TABLE>
 
Upvote 0
That's because in your example, you have a date in B5 and D5 which you said you wanted to show error for.
 
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
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
501
VBAEXCELNew
V
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
921
epactheactor
E

Forum statistics

Threads
1,224,568
Messages
6,179,600
Members
452,927
Latest member
whitfieldcraig

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