Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-04-2014, 04:32 PM
ConfuddledOne ConfuddledOne is offline calculate date if date entered in cell, do nothing if blank Windows 7 64bit calculate date if date entered in cell, do nothing if blank Office 2007
Novice
calculate date if date entered in cell, do nothing if blank
 
Join Date: Oct 2014
Posts: 10
ConfuddledOne is on a distinguished road
Question calculate date if date entered in cell, do nothing if blank


I am trying to work out a formula to display a second date if a date is entered into a specific cell, but to leave it blank if nothing is entered.
In C2 we enter the date a participant has taken a break from our program, they have 28 days to return before they are exited, in D2 I want to calculate the date of the last day they can return.
=C2+28 works fine for displaying the end date, but when dragged down returns 28/01/1900 when there is no date entered in C2, which makes for a confusing spreadsheet
I need a formula that will only return a date in D2 if C2 has a date entered
Any help really appreciated
Reply With Quote
  #2  
Old 11-06-2014, 10:21 AM
Chappster Chappster is offline calculate date if date entered in cell, do nothing if blank Windows 8 calculate date if date entered in cell, do nothing if blank Office 2010 32bit
Novice
 
Join Date: Nov 2014
Posts: 6
Chappster is on a distinguished road
Default

You can try something like =IF(C2="","",C2+28) in D2, that looks to see if there is anything in C2 and if there isn't it clears the cell. However if they put something in there that isn't a date you'll get #VALUE!. To prevent this, if the only thing you want in there is a date, you can click on C2, then click on data menu, then click Data Validation. A pop up should come up, set Allow: to "Date", Data to "greater than" and then Start Date to 1\1\2014 or what ever date you want. Now when someone puts something in there other than a date, they will get a popup that says the value they entered is not valid. they can retry, cancel or select help. If you put both of these together you should have what you're looking for.
Reply With Quote
  #3  
Old 11-06-2014, 01:56 PM
ConfuddledOne ConfuddledOne is offline calculate date if date entered in cell, do nothing if blank Windows 7 64bit calculate date if date entered in cell, do nothing if blank Office 2007
Novice
calculate date if date entered in cell, do nothing if blank
 
Join Date: Oct 2014
Posts: 10
ConfuddledOne is on a distinguished road
Default

Works perfectly, thank you.

I dont know why I have so much trouble with IF formulas
Reply With Quote
  #4  
Old 11-07-2014, 09:37 AM
Chappster Chappster is offline calculate date if date entered in cell, do nothing if blank Windows 8 calculate date if date entered in cell, do nothing if blank Office 2007
Novice
 
Join Date: Nov 2014
Posts: 6
Chappster is on a distinguished road
Default

Glad to hear it!
Reply With Quote
Reply

Tags
date field calculation, if function

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Date field inserting merge date in blank field Lesley Mail Merge 5 09-30-2013 01:49 AM
Formula to calculate Date and Time funkyfido Excel 2 06-25-2013 04:25 PM
calculate date if date entered in cell, do nothing if blank how to calculate backwards from a set finish date ketanco Project 1 02-07-2013 06:50 AM
Make table cell active add date count date MelHerndon Word VBA 3 01-21-2013 04:23 PM
calculate date if date entered in cell, do nothing if blank Calculate date in word field edwedw Mail Merge 3 11-30-2011 02:10 PM


All times are GMT -7. The time now is 03:41 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft