<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:TrackMoves/> <w:TrackFormatting/> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:DoNotPromoteQF/> <w:LidThemeOther>EN-US</w:LidThemeOther> <w:LidThemeAsian>X-NONE</w:LidThemeAsian> <w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:SplitPgBreakAndParaMark/> <w:DontVertAlignCellWithSp/> <w:DontBreakConstrainedForcedTables/> <w:DontVertAlignInTxbx/> <w:Word11KerningPairs/> <w:CachedColBalance/> </w:Compatibility> <m:mathPr> <m:mathFont m:val="Cambria Math"/> <m:brkBin m:val="before"/> <m:brkBinSub m:val="--"/> <m:smallFrac m:val="off"/> <m:dispDef/> <m:lMargin m:val="0"/> <m:rMargin m:val="0"/> <m:defJc m:val="centerGroup"/> <m:wrapIndent m:val="1440"/> <m:intLim m:val="subSup"/> <m:naryLim m:val="undOvr"/> </m:mathPr></w:WordDocument> </xml><![endif]--> Hi, I am relatively new to MS Excel and am trying to create three columns with specific formulae to add days and subtract days in them.
So far I have succeeded, but have encountered a problem when the first cell is left blank. The formula ends up as the sum of zero, as far as I have read somewhere online, which in turn creates a faux date of January 1, 1900. How can I rectify that?
To illustrate what I mean, here are the excel rows I mentioning with their respective formulae:
Column A1:
Entry Date: [No formula needed here] - date is entered manually in this cell
Column B1:
Expiry Date: [Formula: =A1+14] Adds 14 days to Entry Date - Cell (A1)
Column C1:
Final Renewal Date: [Formula: =B1-1] Subtracts 1 day from Expiry Date - Cell (B1)
The formula and cells work fine as long as a date is entered in A1 (Entry Date). The problem arises when that cell (A1) is left blank. If left blank the date January 14, 1900 in B1 and January 13, 1900 in C1 are automatically populated.
All three cells; A1, B1 & C1 were selected and formatted as Number --> Date.
How can I make these cells, B1 & C1 remain blank when A1 is left blank?
Thanks in advance for any tips and assistance.
Kevin A.
So far I have succeeded, but have encountered a problem when the first cell is left blank. The formula ends up as the sum of zero, as far as I have read somewhere online, which in turn creates a faux date of January 1, 1900. How can I rectify that?
To illustrate what I mean, here are the excel rows I mentioning with their respective formulae:
Column A1:
Entry Date: [No formula needed here] - date is entered manually in this cell
Column B1:
Expiry Date: [Formula: =A1+14] Adds 14 days to Entry Date - Cell (A1)
Column C1:
Final Renewal Date: [Formula: =B1-1] Subtracts 1 day from Expiry Date - Cell (B1)
The formula and cells work fine as long as a date is entered in A1 (Entry Date). The problem arises when that cell (A1) is left blank. If left blank the date January 14, 1900 in B1 and January 13, 1900 in C1 are automatically populated.
All three cells; A1, B1 & C1 were selected and formatted as Number --> Date.
How can I make these cells, B1 & C1 remain blank when A1 is left blank?
Thanks in advance for any tips and assistance.
Kevin A.
