You are here:

# Excel/calculating days between dates and subtracting a raw number

Question
QUESTION: Hi Aidan,

We corresponded about a month ago on this same topic/excel document and, as discussed then, I have another twist/tweak I need help with.  I have a formula that is calculating the number of days between two dates as of today's date.  I now need to add a column in which a number, like 10 (for 10 days), may be added and, if it is, needs to be subtracted from the total of the formula.  I tried acouple things and it worked in some cells and not others.  As you suggested last time, I will be sending you an email with a copy of the excel document and a few screen shots that will hopefully help.

Dave

ANSWER: The formula should be (taking G4 as the test)

=IF(C4=0,"",IF(ISBLANK(E4),TODAY()-C4-F4,""))

In your suggested example, you had the -F4 outside the IF condition which was why it was failing.  You also had an AND statement, but this only had one condition within it so the AND was redundant.

---------- FOLLOW-UP ----------

QUESTION: Hi Aidan,

I have a follow up that I don't even know how to describe.  Will email you an updated copy of the document and you'll see what I'm inquiring about on the 3rd tab/worksheet entitled "PC RA days to complete".  My attempts to tweak what you gave me yesterday (which worked great by the way) and apply it to this new request have not worked.

The formula had been entered as an array formula (ctrl shift enter rather than just enter), which it didn’t need to be – all formulas should be normal formulas in this case.  NOTE also that your use of the datevalue function is fine as long as all users have the same date system – as I’m in the UK it generates an error as we write dates differently – to be date system independent use the DATE function which translates the year, month and day into a date serial eg DATE(2014,1,31)
Questioner's Rating
 Rating(1-10) Knowledgeability = 10 Clarity of Response = 10 Politeness = 10 Comment Thank you, Aidan. Sorry I didn't see/catch that. Dave

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

#### Aidan Heritage

##### Expertise

I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for all versions from 2 onwards. I'm based in the UK, so please allow for time differences when asking me questions from other parts of the world!

##### Experience

My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.

Education/Credentials
I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!