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

Use Excel to calculate how many days to an event



Excel handles dates as integers. This means that you can add and subtract them. This can be helpful in telling you how many days are left until your next appointment or event. In this article, we'll show you how to use the DATE, YEAR, MONTH, DAY, and TODAY functions of Excel to calculate how many days to go before your next birthday or any other event.

Excel saves dates as integers. By default, Excel uses "1

" to represent 1/1/1900, and each day thereafter is one greater. Enter 01/01/2000 and change the format to "Number". This will display "36526". If you subtract 1 from 36526, you can see that there were 36525 days in the 20th century. Alternatively, you can enter a future date and subtract the result of the TOD function to see how many days to date are the date.

A Brief Summary of Date-Related Functions

Before We Dig into Some Examples Let's go through some simple date-related functions, including Excel's TODAY, DATE, YEAR, MONTH, and DAY functions.

TODAY

Syntax: = TODAY ()

Result: The current date

DATE

Syntax: = DATE (year, month, day)

Result: The year, month, and month Day Date Entered

YEAR

Syntax: = YEAR (date)

Result: The year of the entered date

MONTH

Syntax: = MONTH (date)

Result: The numeric month of the month entered date (1 to 12)

DAY

Syntax: = DAY (date)

Result: The day of the month of the entered date

Some example calculations

We look at three events that occur annually at the same time Day and calculate the date of th your next occurrence and determine the number of days between now and the next occurrence.

Here are our sample data. We've set up four columns: Event, Date, Next_Occurrence, and Days_Until_Next. We entered the dates of a random birth date, the date taxes are due in the US, and Halloween. Data such as birthdays, anniversaries, and some holidays are displayed on certain days each year and work well with this example. Other holidays – such as Thanksgiving – take place on a particular day of the week in a given month; This example does not cover these types of events.

 Sample Data

There are two ways to fill in the Next_Occurrence column. You can enter each date manually, but each entry must be manually updated in the future when the date has passed. Instead, write an IF statement formula so that Excel can do the work for you.

Let's take a look at the birthday. We know the month = MONTH (F3) and the day = DAY (F3) of the next occurrence. That's easy, but what about the year? We need Excel to know if the birthday has already taken place this year or not. First, we need to use this formula to calculate the date on which the birthday will occur in the current year:

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

Next we want You'll need to know if this date has already passed, and you can compare that result with TODAY () to find out. If it is July and the birthday takes place every year in September, the next occurrence is in the current year, presented with = YEAR (TODAY () (1945)) . If it is December and the birthday takes place every year in May, the next occurrence will be next year, so that = YEAR (TODAY ()) + 1 would result in the next year. To determine which method to use, we can use an & # 39; IF & # 39; statement:

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

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

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

 Enter = TODAY (), YEAR (TODAY ()), YEAR (TODAY ()) + 1), MONTH (F3), DAY (F3)) "in 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);”/>

Press Enter to see the result. (This article was written at the end of January 2019, so the dates are now … dated.)

Fill this formula into the cells below by highlighting the cells and pressing Ctrl + D.

 Next Occurrence Results

Now you can set the number of days to Easily determine the next occurrence by subtracting the result of the TODAY () function from the Next_Occurrence results just calculated. Enter the following formula in cell H3:

  = G3-TODAY () 

 Enter " = G3-TODAY () "in Z elle H3

Press Enter to see the result, and fill that formula into the cells below by highlighting the cells and pressing Ctrl + D. [19659003]   Days_Until_Next Results

You can save a workbook with the formulas in this example to keep track of who has a birthday next, or how many days you have left to finish your Halloween costume. Each time you use the workbook, the results are recalculated based on the current date because you used function TODAY () .

 Sample Results

And yes These are pretty specific examples that may or may not be useful to you. However, they are also an illustration of the capabilities that you can do with date-related functions in Excel.




Source link