showing number of days apart from today

Enzo_Matrix

Board Regular
Joined
Jan 9, 2018
Messages
113
I am updating a table for a production schedule and I want to automatically tell how many days away an order is due from today without looking at the dates and counting.
Is there something that can do this while still keeping the date as well?

Jan 1, 2018 - 9
Jan 10, 2018 - Today
Jan 12, 2018 +2
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,950
Office Version
365, 2010
Platform
Windows
You can just subtract them.

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;">1/1/2018</td><td style="text-align: right;;">-9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">1/10/2018</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">1/12/2018</td><td style="text-align: right;;">+2</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B1</th><td style="text-align:left">=A1-$A$2</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B3</th><td style="text-align:left">=A3-$A$2</td></tr></tbody></table></td></tr></table><br />

Format the cells as:
+0;-0;0
 
Last edited:

Roderick_E

Well-known Member
Joined
Oct 13, 2007
Messages
2,051
Let's suppose your dates are in column B, on each row. In column C you could have this code
Code:
=b1-today()
Drag down the formula

Format column C as general or number. It will give you how many days you have until the date in column B.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,950
Office Version
365, 2010
Platform
Windows
Are you actually subtracting from TODAY() or from the cell in A2 as in your example?
 

Enzo_Matrix

Board Regular
Joined
Jan 9, 2018
Messages
113
I do have the dates listed in Column 'G' and in column 'H' I want to show a difference, numerically, from the listed date to today's.

I manually input the formula and it does work, but not as cleanly as I'd like
Code:
=$G2-Today()
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,950
Office Version
365, 2010
Platform
Windows
This will put the value in H2 downward for every value in column G:

Code:
Sub TodayDifference()
With Range("H2:H" & Range("G" & Rows.Count).End(xlUp).Row)
    .Formula = "=G2-Today()"
    .Value = .Value
    .NumberFormat = "+0;-0;0"
End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,095,372
Messages
5,444,076
Members
405,265
Latest member
Iram

This Week's Hot Topics

Top