How Do I Show # of Days Before Today's Date?

[bencar]

In one column in my sheet it says '=TODAY()-31' when I click in it. The date happens to be 31 days before today's date. How do I format a column of past dates like this so that when I click in the cell I see '=TODAY()-31' where the '-31' part shows the amount of days before today's date?

 

[RasGhul]

In one column in my sheet it says '=TODAY()-31' when I click in it. The date happens to be 31 days before today's date. How do I format a column of past dates like this so that when I click in the cell I see '=TODAY()-31' where the '-31' part shows the amount of days before today's date?

So you want to show number of days between past dates and today's date?

 

[RasGhul]

In one column in my sheet it says '=TODAY()-31' when I click in it. The date happens to be 31 days before today's date. How do I format a column of past dates like this so that when I click in the cell I see '=TODAY()-31' where the '-31' part shows the amount of days before today's date?

=DAYS(TODAY(),A1)

A1 is your past dates column

 

[bencar]

=DAYS(TODAY(),A1)

A1 is your past dates column

No, no thats not exactly what I mean. Its best that you look at the sheet itself. The link is posted below. Go to the Invoice Data tab. Then click any date in the 'Date' tab. When you click in one of those cells, you see =TODAY()-95, or =TODAY()-31, or =TODAY()-6, etc etc. How can I do that with my list of dates?

https://drive.google.com/open?id=1Y5JrZDxe8l2xckOrdBynvg3yUN2MGfxa

 

[bencar]

Strange Formatting When Clicking a Cell

In one column in my sheet it says '=TODAY()-31' when I click in it. The date happens to be 31 days before today's date. How do I format a column of past dates like this so that when I click in the cell I see '=TODAY()-31' where the '-31' part shows the amount of days before today's date?

Heres my excel file. Please look at teh Date column and click in one of the cells to see what I mean:
https://www.mrexcel.com/forum/redir...com/open?id=1Y5JrZDxe8l2xckOrdBynvg3yUN2MGfxa

 

[Fluff]

Re: Strange Formatting When Clicking a Cell

Select the top date in the column, then doubleclick the square box in the bottom r/h corner of the selection box

 

[bencar]

Re: Strange Formatting When Clicking a Cell

Select the top date in the column, then doubleclick the square box in the bottom r/h corner of the selection box

What are you talking about? Why would I paste down all these values down the date column?

What I'm asking is how do I apply this function: =TODAY()-31 to past dates in a column which will give me -28, or -12, or -55 at the end? For example, I got 2/28/2018. How do I apply that formula in that date so when I click on the cell it gives me =TODAY()-44 ?

 

[StephenCrump]

Re: Strange Formatting When Clicking a Cell

In essence, this is what you've got:

B5: =TODAY()-31
B6: =TODAY()-95
B7: =TODAY()-6

C5: =TODAY()-B5 (copied down the column)

It's no surprise that column C simply gives you back the numbers you have hard-coded into the column B formula. Where do these numbers 31,95,6 etc come from?

Surely the days outstanding in column C should be a formula based on the invoice due date? At the moment, these are all shown as falling in 2012?

Book1
	A	B	C
1
2	Today	14 Apr 2018
3
4	Invoice	Date	Days O/S
5	1001	14 Mar 2018	31
6	1002	9 Jan 2018	95
7	1003	8 Apr 2018	6
Sheet1

 

[bencar]

Re: Strange Formatting When Clicking a Cell

In essence, this is what you've got:

B5: =TODAY()-31
B6: =TODAY()-95
B7: =TODAY()-6

C5: =TODAY()-B5 (copied down the column)

It's no surprise that column C simply gives you back the numbers you have hard-coded into the column B formula. Where do these numbers 31,95,6 etc come from?

Surely the days outstanding in column C should be a formula based on the invoice due date? At the moment, these are all shown as falling in 2012?

	A	B	C
Today

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]14 Apr 2018[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]Invoice[/TD]
[TD="align: right"]Date[/TD]
[TD="align: right"]Days O/S[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1001[/TD]
[TD="align: right"]14 Mar 2018[/TD]
[TD="align: right"]31[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1002[/TD]
[TD="align: right"]9 Jan 2018[/TD]
[TD="align: right"]95[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1003[/TD]
[TD="align: right"]8 Apr 2018[/TD]
[TD="align: right"]6[/TD]

</tbody>

I'm very confused by your answer. I entered 3/31/2018 in A1. In A2 I entered: =TODAY()-A1 hoping to get =TODAY()-14 which would be 3/31/2018. But the answer came out as: 1/14/1900.

To answer your other question as to where I got these numbers 31,95,6... These are the number of days from today's date in the spreadsheet I linked. Didnt you see this??

Again I'll make it simple.. Lets say I got this below. How do i get each cell to give me =TODAY()-'number of days from todays date' when I click in the cell?

Book1
	A
1	2/11/2018
2	2/22/2018
3	3/2/2018
Sheet1

 

[AhoyNC]

Re: Strange Formatting When Clicking a Cell

Just change your format to GENERAL or NUMBER and you will get 14.