Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-17-2021, 05:59 AM
Joanne Joanne is offline Problem writing Excel formula between two dates Windows 7 32bit Problem writing Excel formula between two dates Office 2010 32bit
Advanced Beginner
Problem writing Excel formula between two dates
 
Join Date: Jul 2013
Posts: 58
Joanne is on a distinguished road
Default Problem writing Excel formula between two dates

Hello

I am trying to put a formula into a spreadsheet in order to calculate the number of days a project takes to complete. In some cases, I have both dates, in some a start date and in others a date when the job needs completing by. I want the No of Days cell to remain empty unless I have both dates. I have used the IF function but I think I need to add an OR but can't get it to work. Can someone please help me?


Start Date End Date No of Days
01/01/2021 31/12/2021 365
31/12/2021
01/01/2021 -44196



Many thanks for looking.
Reply With Quote
  #2  
Old 12-18-2021, 12:47 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Problem writing Excel formula between two dates Windows 7 64bit Problem writing Excel formula between two dates Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Try
Code:
=IF(OR(A1="",B1=""),"",B1-A1)
Be aware that if you need the value in col C for further calculations the "" result will probably trigger errors
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #3  
Old 12-18-2021, 02:41 AM
Joanne Joanne is offline Problem writing Excel formula between two dates Windows 7 32bit Problem writing Excel formula between two dates Office 2010 32bit
Advanced Beginner
Problem writing Excel formula between two dates
 
Join Date: Jul 2013
Posts: 58
Joanne is on a distinguished road
Default

Thank you Pecoflyer, I have copied the formula but I am still getting #VALUE! in column C when a start date or an end date is entered. How can I stop this appearing?
Reply With Quote
  #4  
Old 12-18-2021, 02:55 AM
Joanne Joanne is offline Problem writing Excel formula between two dates Windows 7 32bit Problem writing Excel formula between two dates Office 2010 32bit
Advanced Beginner
Problem writing Excel formula between two dates
 
Join Date: Jul 2013
Posts: 58
Joanne is on a distinguished road
Default

The only way I can get it to work is to type =IFERROR(C3," ") and then hide column C and use the results in column D as the "No of Days" result. Is there an easier way, ie, can I combine IFERROR with the original formula you sent to me?
Reply With Quote
  #5  
Old 12-18-2021, 04:32 AM
Joanne Joanne is offline Problem writing Excel formula between two dates Windows 7 32bit Problem writing Excel formula between two dates Office 2010 32bit
Advanced Beginner
Problem writing Excel formula between two dates
 
Join Date: Jul 2013
Posts: 58
Joanne is on a distinguished road
Default

Sorry, that I left a bit out and it didn't make sense:
The only way I can get it to work is to type =IFERROR(C3," ") in column D and then hide column C and use the results in column D as the "No of Days" . Is there an easier way, ie, can I combine IFERROR with the original formula you sent to me?
Reply With Quote
  #6  
Old 12-18-2021, 07:20 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Problem writing Excel formula between two dates Windows 7 64bit Problem writing Excel formula between two dates Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Please post a sample sheet
And also see if the cell references used match your data
Are your dates real dates ( right aligned in cell ) or text looking like dates??
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #7  
Old 12-19-2021, 01:27 AM
Joanne Joanne is offline Problem writing Excel formula between two dates Windows 7 32bit Problem writing Excel formula between two dates Office 2010 32bit
Advanced Beginner
Problem writing Excel formula between two dates
 
Join Date: Jul 2013
Posts: 58
Joanne is on a distinguished road
Default

I am not sure how to upload a copy of the file. I hope this works.
Attached Files
File Type: xlsx Project Dates.xlsx (10.5 KB, 8 views)
Reply With Quote
  #8  
Old 12-19-2021, 08:58 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Problem writing Excel formula between two dates Windows 7 64bit Problem writing Excel formula between two dates Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

When you the formula returns a "" and you add 1, it returns an error as "" is text
Try
Code:
=IF(OR(A1="",B1=""),"",B1-A1+1)

For reasons unknown cell A4 is not empty but it contains a space " " which again returns an error as ""is not the same as " " ( a cell with a space is not empty)

I suppose this is a mistake??
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #9  
Old 12-20-2021, 02:15 AM
Joanne Joanne is offline Problem writing Excel formula between two dates Windows 7 32bit Problem writing Excel formula between two dates Office 2010 32bit
Advanced Beginner
Problem writing Excel formula between two dates
 
Join Date: Jul 2013
Posts: 58
Joanne is on a distinguished road
Default

That works perfectly. Thank you so much for your help.
Reply With Quote
  #10  
Old 12-21-2021, 01:03 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Problem writing Excel formula between two dates Windows 7 64bit Problem writing Excel formula between two dates Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Thanks for the rep
I marked the thread as " solved " for you
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem writing Excel formula between two dates MS Excel formula problem tabbymulla Excel 1 05-10-2018 12:08 AM
Macro or formula on Excel to Standardize dates leonardo.sp05 Excel 7 02-13-2017 06:15 PM
Problem writing Excel formula between two dates Excel formula problem/ Help required dmcg9760 Excel 5 09-10-2015 09:42 PM
Problem writing Excel formula between two dates formula writing for extracting data jennamae Excel 1 11-15-2013 08:40 PM
font for the formula I am writing vthomeschoolmom Word 1 01-30-2013 10:40 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:19 PM.


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