dynamic number of days

snking

New Member
Joined
Mar 11, 2019
Messages
3
Hello everyone,

I am trying to make an excel spreadsheet that will do a couple of things. The first is it will tell me how many days have passed since a specific date. For example, if B2 is 1/24/2019, then C2 (which is where I want the number to be) will be a dynamic number that goes up by one as each day pass. So on 1/25/2019, C2 will have the number 1 in it. And on 1/26/2019, it will be 2, and it will go on from there. D2 and E2 would have the same relationship as C2 and D2. Meaning I need for E2 to tell me how many days based on D2. I would need to duplicate this formula all the down to M2 and N2. This will tell me how many days since I serviced a customers' property (column C) and what is the next approximate service date (column D). I hope this makes sense, I know nothing of how to create formulas in excel. Thanks in advance for any help...

S.
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,773
Office Version
  1. 365
Platform
  1. Windows
I do not follow some of your explanation, so here is an explanation to help you create your own formulas
Some formatting occurs automatically, but sometimes you must do yourself by Right-Clicking on cell \ Format Cells \ etc

C2
formula
=TODAY()-B2
format C2 as a NUMBER with no decimal places

D2
If next service is due in 45 days, calculate the next sevice date like this
=B2+45
format D2 as date

To work out how many days to the next service, where D2 contains next service date
E2
formula
=D2-TODAY()
format E2 as a NUMBER with no decimal places

F2
If next service is due in a further 45 days, copy cell D2 and paste in F2
or enter the formula manually
=D2+45
If next service is NOT due in 45 days, then replace the 45 with the appropriate number
format F2 as date

G2
Copy cell E2 and paste in G2
or enter the formula manually
=F2-TODAY()
format G2 as a NUMBER with no decimal places
 
Last edited:

snking

New Member
Joined
Mar 11, 2019
Messages
3
Hey Yougle,

Thanks for the response. I'm going to try to create a formula based on what you have told me. Hopefully, it will work, thanks again.


Shawn,



I do not follow some of your explanation, so here is an explanation to help you create your own formulas
Some formatting occurs automatically, but sometimes you must do yourself by Right-Clicking on cell \ Format Cells \ etc

C2
formula
=TODAY()-B2
format C2 as a NUMBER with no decimal places

D2
If next service is due in 45 days, calculate the next sevice date like this
=B2+45
format D2 as date

To work out how many days to the next service, where D2 contains next service date
E2
formula
=D2-TODAY()
format E2 as a NUMBER with no decimal places

F2
If next service is due in a further 45 days, copy cell D2 and paste in F2
or enter the formula manually
=D2+45
If next service is NOT due in 45 days, then replace the 45 with the appropriate number
format F2 as date

G2
Copy cell E2 and paste in G2
or enter the formula manually
=F2-TODAY()
format G2 as a NUMBER with no decimal places
 

snking

New Member
Joined
Mar 11, 2019
Messages
3
Hey Yongle,

Thanks again for the formulas, they worked like a charm. I really appreciate all your help and everyone at Mr.Excell.....

Shawn,
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,773
Office Version
  1. 365
Platform
  1. Windows
thanks for the feedback (y)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,363
Messages
5,528,263
Members
409,811
Latest member
pjwhyman

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top