Start date, Today's date, Cumulative days between the two



To subtract Today from Start the date has to be reformatted to a number. Ordinary humans (me) find it difficult to readily grasp the #'s meaning.

So does that then entail double columns for each date--one formatted as a date to understand visually the date and the other as a number for the calculation?

Also, if I enter the keyboard shortcut for Today's date, save and then reopen the sheet the next day will said Today's date be automatically updated? But here's the clinker, that Today's date shortcut entered a visual date--I need a Today's date that the math will work with?

Mark

Dates you see in Excel are only formatting. The value XL works with to do any operation is the underlying value ( number of days since Jan st 1900). So you shouldn't worry about these values. Just go ahead and perform operations on the dates

Yes, = TODAY() will give another result tomorrow, no, if entered with a kb shortcut it will not

Beware though, if what you see is a real date or text looking like a date ! The first one right aligns in the cell, the second left aligns as all text does.

__________________
Using O365 v2503 - 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

Didn't work at this end. Here's what I did:

Start date cell: 9/2/2015, Current date cell: = TODAY() which became 5/5/2016, Cumulative days cell: +Current-Start equaled 9/2/1900?

The result being a number of days, you should format that cell as number.

To understand better how XL works with dates have a look at http://www.cpearson.com/excel/datetime.htm#AddingDates

( there are many other very good explanations on the web)

But don't forget, XL is not a WYSIWYG

__________________
Using O365 v2503 - 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

Well that was sure easy.

I double checked one of the answers against a web date calculator. I told the calculator to include the current date as a day in the subtraction which resulted in it's value being 1 day greater than XL.

I've searched Options and I (admit to having) scanned your help link (that I'm saving for reference) and don't see an available modification?

What do you mean?

__________________
Using O365 v2503 - 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

There are two methods of adding/subtracting dates.

1. 5/6/16-5/1/16=5
2. 5/6/16-5/1/16=6

The second example you include the last date in value, the first you do not. The spreadsheet returns '5', the internet calculator when told to include the last day returns '6'.

In the following I work with the format dd-mm-yyyy

With 02-09-2015 in A1 and
05-05-2016 in A2
then in A3 try to enter
=A2-A1 which I think returns 02-09-1900.

Now try to format cell A3 General. and you should get the number 246 which is the number of days from 02-09-2015 to 05-05-2016.

If you need to include one day, just add 1 to the difference

__________________
Using O365 v2503 - 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

Admittedly focused on (to me) an 'exotic' solution ;-)

Thanks--

Mark