Microsoft Office Forums Problem writing Excel formula between two dates

#1
12-17-2021, 05:59 AM
 Joanne Windows 7 32bit Office 2010 32bit Advanced Beginner Join Date: Jul 2013 Posts: 58
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.
#2
12-18-2021, 12:47 AM
 Pecoflyer Windows 7 64bit Office 2010 Expert Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,809

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
#3
12-18-2021, 02:41 AM
 Joanne Windows 7 32bit Office 2010 32bit Advanced Beginner Join Date: Jul 2013 Posts: 58

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?
#4
12-18-2021, 02:55 AM
 Joanne Windows 7 32bit Office 2010 32bit Advanced Beginner Join Date: Jul 2013 Posts: 58

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?
#5
12-18-2021, 04:32 AM
 Joanne Windows 7 32bit Office 2010 32bit Advanced Beginner Join Date: Jul 2013 Posts: 58

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?
#6
12-18-2021, 07:20 AM
 Pecoflyer Windows 7 64bit Office 2010 Expert Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,809

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
#7
12-19-2021, 01:27 AM
 Joanne Windows 7 32bit Office 2010 32bit Advanced Beginner Join Date: Jul 2013 Posts: 58

I am not sure how to upload a copy of the file. I hope this works.
Attached Files
 Project Dates.xlsx (10.5 KB, 8 views)
#8
12-19-2021, 08:58 AM
 Pecoflyer Windows 7 64bit Office 2010 Expert Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,809

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
#9
12-20-2021, 02:15 AM
 Joanne Windows 7 32bit Office 2010 32bit Advanced Beginner Join Date: Jul 2013 Posts: 58

That works perfectly. Thank you so much for your help.
#10
12-21-2021, 01:03 AM
 Pecoflyer Windows 7 64bit Office 2010 Expert Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,809

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

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post tabbymulla Excel 1 05-10-2018 12:08 AM leonardo.sp05 Excel 7 02-13-2017 06:15 PM dmcg9760 Excel 5 09-10-2015 09:42 PM jennamae Excel 1 11-15-2013 08:40 PM vthomeschoolmom Word 1 01-30-2013 10:40 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:47 PM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top