#1
|
|||
|
|||
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
|
||||
|
||||
Try
Code:
=IF(OR(A1="",B1=""),"",B1-A1)
__________________
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
||||
|
||||
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 |
#7
|
|||
|
|||
I am not sure how to upload a copy of the file. I hope this works.
|
#8
|
||||
|
||||
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
|
|||
|
|||
That works perfectly. Thank you so much for your help.
|
#10
|
||||
|
||||
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 |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
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 |
Excel formula problem/ Help required | dmcg9760 | Excel | 5 | 09-10-2015 09:42 PM |
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 |