قالب وردپرس درنا توس
Home / Tips and Tricks / Use Excel to calculate how many days up to an event

Use Excel to calculate how many days up to an event



Excel treats dates as integers. This means that you can add and subtract them, which can be useful for telling how many days there are up to the next deadline or event. In this article, we will use Excel's DATE, YEAR, MONTH, DAY and DAY functions to show you how to calculate the number of days until your next birthday or any other yearly event.

The Excel stores are listed as integers. By default, Excel uses "1

" to represent 01/01/1900 and every day after that is a larger one. Enter 01/01/2000 and change the format to "Number" and you will see "36526" displayed. If you subtract 1 from 36526, you can see that there were 36525 days in the 20s. Alternatively, you can enter a future date and subtract the result of the TODAY function to see how many days away that date is from today.

A Quick Summary of Date-related Functions

Before entering any examples, we need to go over several simple date-related features, including Excel's DAY, DATE, YEAR, MONTH and DAY functions.

TODAY

Syntax: = TODAY ()

Result: The current date

DATE

Syntax: = DATE (year, month, day)

Result: The date is given year, month and day entered

YEAR

Syntax: = YEAR (date)

Result: Date of entry

MONTH

Syntax: = MONTH (date)

Result: The numeric month of the specified day (1 to 12)

DAY

Syntax: = DAY (date)

Result: Date of the month for the specified day

Some sample calculations

We will look at three events that occur annually the same day, calculate the date of the next event and determine the number of days between now and their next event.

Here are our sample data. We have four columns set up: Event, Date, Next_Occurrens and Days_Until_Next. We have set dates for a random date of birth, date tax depends on USA and Halloween. Dates like birthdays, anniversaries and some holidays occur on specific days each year and work well with this example. Other holidays like Thanksgiving occur on a particular weekday of a particular month; this example does not include such types of events.

 Example Data

There are two options for filling in the column Next_Occurrence. You can enter each date, but each entry must be updated manually in the future when the date passes. Instead, let us write an "IF" statement formula so that Excel can do the job for you.

Let's look at the birthday. We already know the month = MONTH (F3) and day = DAY (F3) of the next event. It's simple, but how much with the year? We need Excel to know if the birthday has occurred this year already or not. First, we must calculate the date on which the birthday occurs during this year with the following formula:

  = DATE (YEAR (DAY ()), MONTH (F3), DAY (F3)) 

Then need to know if that date already has gone and you can compare that result to TODAY () to find out. If it is July and the birthday occurs every September, the next event is the current year, shown by = YEAR (TODAY ()) . If it is December and the birthday occurs every May, then the next event next year, so = YEAR (DAY ()) + 1 would be next year. To determine which ones to use, we can use an IF statement:

  = IF (DATE (YEAR (DAY ()), MONTH (F3), DAY (F3))> = DAY (), YEAR)), YEAR (TODAY ()) + 1) 

Now we can combine the results of the IF declaration with the MONTH and the DAY of the birthday to determine the next event. Enter this formula in cell G3:

  = DATE (OM (DATE (DAY ()), MONTH (F3), DAY (F3))> = DAY (), YEAR (DAY ()), YEAR (), MONTH (F3), DAY (F3)) 

 Input = DAY (), YEAR (DAY ()), YEAR (DAY ()) + 1), MONTH (F3 ), DAY (F3)) "to cell F3 ” width=”650″ height=”250″ src=”/pagespeed_static/1.JiBnMqyl6S.gif” onload=”pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);” onerror=”this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);”/>

Click Enter to see the result. (This article was written in January 2019, so the dates will be … well … dated.) [19659002] Fill this formula into the cells below by selecting the cells and pressing Ctrl + D.

 Next Occurrence Result

Now we can easily determine the number of days until the next event by subtracting the result of the function TODAY () from the Next_Occurrence results we just calculated Write the following formula in cell H3:

  = G3-DAY () 

 Type "= G3-TODAY ()" in cell H3

Press Enter to see the result and then fill this formula down into the cells by selecting the cells and pressing Ctrl + D. [19659003]   Days_Until_Next Results

You can save a workbook of the formulas in this example to keep track of whose birthday is coming up next or know how many days you have Have left to finish your Halloween costume. Each time you use the workbook, it will recalculate the results based on the current date because you have used the TODAY () function .

 Example Results

And yes, these are quite specific examples that may or may not be useful to you. But they also serve to illustrate what things you can do with date-related functions in Excel.




Source link