Дата сегодня эксель - Учим Эксель

How Dates Work in Excel; The Calendar System Explained Video

How Dates Work in Excel – The Calendar System Explained + Video

Bottom line: With Valentine’s Day rapidly approaching I thought it would be good to explain how you can get a date with your Excel skills. Just kidding! This post and video explain how the date calendar system works in Excel.

Skill level: Beginner

Learn How Dates Work in Excel

Dates in Excel can be just as complicated as your date for Valentine’s Day. We are going to stick with dates in Excel for this article because I’m not qualified to give any other type of dating advice.

Video Tutorial on How Dates Work in Excel

The following is a video from The Ultimate Lookup Formulas Course on how the date system works in Excel.

There are over 100 short videos just like the one above included in the Ultimate Lookup Formulas Course.

This course has been designed to help you master Excel’s most important functions and formulas in an easy step-by-step manner.

What is a Date in Excel?

I should first make it clear that I am referring to a date that is stored in a cell.

The dates in Excel are actually stored as numbers, and then formatted to display the date. The default date format for US dates is “m/d/yyyy” (1/27/2016).

Excel Dates Are Stored as Serial Numbers and Formatted as Dates

The dates are referred to as serial numbers in Excel. You will see this in some of the date functions like DAY(), MONTH(), YEAR(), etc.

So then, what is a serial number? Well let’s start from the beginning.

The date calendar in Excel starts on January 1st, 1900. As far as Excel is concerned this day starts the beginning of time.

Each Day is a Whole Number

Each day is represented by one whole number in Excel. Type a 1 in any cell and then format it as a date. You will get 1/1/1900. The first day of the calendar system.

Type a 2 in a cell and format it as a date. You will get 1/2/1900, or January 2nd. This means that one whole day is represented by one whole number is Excel.

Excels Date System Starts on January 1st 1900

You can also take a cell that contains a date and format it as a number.

For example, this post was published on 1/27/2016. Put that number in a cell (the keyboard shortcut to enter today’s date is Ctrl+;), and then format it as a number or General.

You will see the number 42,396. This is the number of days that have elapsed since 1/1/1900.

Date Based Calculations

It is important to know that dates are stored as the number of days that have elapsed since the beginning of Excel’s calendar system (1/1/1900).

When you calculate the difference between two dates by subtraction, the result will be the number of days between the two dates.

Calculate the Difference Between Two Dates in Excel - Returns Number of Days

1/27/2016 – 1/1/2016 = 26 days

6/30/2016 – 1/1/2016 = 181 days

There are a lot of Date functions in Excel that can help with these calculations. Last week we learned about the DAY function for month-to-date calculations with pivot tables.

Ust the DAY Function to Return Day of Month in the Pivot Table Source Data

We won’t go into all the date functions here, but understanding that the serial number represents one day will give you a good foundation for working with dates.

What About Dates with Times?

Do you ever work with dates that contain time values?

These dates are still stored as serial numbers in Excel. When you convert the date with a time to the number format, you will see a decimal number.

This decimal is a fraction of the day.

The Time Value is Stored as a Fraction of the Serial Number Day

One hour in Excel is represented by the number: 1/24 = 0.04167

One minute in Excel is represented by the number: 1/(24*60) = 1/1440 = 0.000694

So 8:30 AM can be calculated as: (8 * (1/24)) + (30 * (1/1440)) = .354167

An easier way to calculate this is by typing 8:30 AM in a cell, then changing the format to Number.

So if you are running a half hour late and want to let your boss know, text him/her and say you will be there at 0.354167.

Checkout my article on 3 ways to group times in Excel for more date time based calculations.

Don’t Talk About Excel Dates with Your Date

Unless your Valentine shares a similar passion for Excel, I strongly recommend NOT sharing this information on your date.

I remember the first time I met my wife, and told her I worked in finance. The first word out of her mouth was, “BORING!”. Awe… it was love at first sight… LOL

But you should now be able to use Excel to determine how many days it has been since you last spoke to your date. That’s the only dating advice I can give.

7 Ways To Get The Weekday Name From A Date In Excel

It’s pretty common to want to know what day of the week a given date falls on and unless you’ve got some sort of gift for knowing that, you’re going to need a way to figure it out.

In Excel, there are many different ways to determine this. In this post, we’re going to explore 7 ways to achieve this task.

Table of Contents

Format a Date as the Weekday Name

The first option we’re going to look at involves formatting our date cells.

Dates in Excel are really just serial numbers starting at 1 for the date 1900-01-01. Formatting is what makes the date look like a date.

There are many ways to format these serial numbers to display the date in various formats like yyyy-mm-dd, dd/mm/yyyy, dd-mmm-yy etc…

One of the possible formatting options for these serial numbers is to display the weekday name with a custom dddd or ddd format.

We can format our dates from the Format Cells dialog box.

  1. Select the dates which we want to convert into weekday names.
  2. Go to the Home tab and click on the small launch icon in the lower right corner of the Number section. This will open up the Format Cells dialog box. We can also open up the Format Cells dialog a few other ways.
    • The keyboard shortcut Ctrl + 1 .
    • Right click on the selected cells ➜ choose Format Cells from the menu.
  3. Go to the Number tab in the Format Cells dialog box.
  4. Select Custom as the Category.
  5. Add dddd into the Type field for the full weekday name or ddd for the abbreviated weekday name.
  6. Press the OK button.
Интересно почитать:  Смещенное отклонение excel это

Now our dates will appear as the weekday names in the worksheet. The dates are still inside the cells and can be seen in the formula bar when a cell is selected.

Get the Weekday Name with the TEXT Function

The TEXT function will allow us to convert numbers to text and apply formatting to those numbers.

We can use the TEXT function to convert the number 1234 into the text string $1,234 with the above formula.

Since dates are really just serial numbers, we can use this function to convert any date into a text string with the weekday name format.

TEXT Syntax

  • Value (required) is the value to convert to a text string.
  • Format (required) is the formatting to apply when converting to a text string.

The above formula will convert our date value in cell B2 into the corresponding weekday name. In this example we get a value of Friday from the date 2020-09-18.

Get the Weekday Number with the WEEKDAY Function

While the results aren’t quite as useful, there is also a WEEKDAY function in Excel.

This will convert a date into a corresponding number between 1 and 7 representing the weekday.

WEEKDAY Syntax

  • Date (required) the date to find the weekday number from.

  • Type (optional) the weekday number type to return.
    • Omitted or 1 returns 1 for Sunday through 7 for Saturday.
    • 2 returns 1 for Monday through 7 for Sunday.
    • 3 returns 0 for Monday through 6 for Sunday.
    • 11 returns 1 for Monday through 7 for Sunday.
    • 12 returns 1 for Tuesday through 7 for Monday.
    • 13 returns 1 for Wednesday through 7 for Tuesday.
    • 14 returns 1 for Thursday through 7 for Wednesday.
    • 15 returns 1 for Friday through 7 for Thursday.
    • 16 returns 1 for Saturday through 7 for Friday.
    • 17 returns 1 for Sunday through 7 for Saturday.

    The above formula will convert our date value in cell B2 into the corresponding weekday number. The second argument value of 1 will return a 1 for Sunday through to 7 for a Saturday. In this case 2019-09-18 returns a 6 because it’s a Friday.

    Combining SWITCH with WEEKDAY to Return the Weekday Name

    On its own, the WEEKDAY function can only return a number representing the weekday, but we can combine it with the SWITCH function to get the weekday name.

    The WEEKDAY function returns a number from 1 to 7 and we can then use the SWITCH function to assign a weekday name to each of these numbers.

    Get the WEEKDAY Name Using Power Query

    Power Query (also known as Get & Transform) is a powerful data wrangling tool available in Excel 2016 onward.

    It makes any data transformation easy and it can get the name of the weekday too.

    We first need to import our data into the power query editor. We need our data inside an Excel table.

    1. Select a cell inside the Excel table containing the dates.
    2. Go to the Data tab in the ribbon.
    3. Press the From Table/Range command in the Get & Transform Data section.

    This will open up the power query editor.

    We can now transform our dates into the name of the weekday.

    1. We need to make sure the column is converted to the date data type. Click on the icon in the left of the column heading and select Date from the options.
    2. With the date column selected, go to the Add Column tab.
    3. Select DateDayName of Day.

    This will add a new column containing the weekday name and we can see the M code that’s generated in the power query formula bar. This uses the Date.DayOfWeekName power query function.

    A similar command can be found in the Transform tab. The difference is, this will not add a new column, but rather transform the selected column.

    Get the WEEKDAY Name in a Pivot Table with the WEEKDAY DAX Function

    Well, we can go a step further and summarize our dates as a list of weekday names inside our pivot table using a DAX measure!

    We need to create a pivot table from our data.

    1. Select a cell inside the data.
    2. Go to the Insert tab in the ribbon.
    3. Press the PivotTable command.
    4. In the Create PivotTable menu check the option to Add this data to the Data Model and press the OK button.

    This will create a new blank pivot table in the workbook and add the data into the data model. Adding the data to the data model will allow us to use the DAX formula language with our pivot table.

    Now we can create a measure to convert our dates into names and summarize the results into a comma separated list.

    1. Select a cell inside the pivot table.
    2. Right click on the table in the PivotTable Fields window and select Add Measure from the menu options.

    This will open up the DAX formula editor and we can create our DAX measure.

    We can now add the following formula into the DAX formula editor.

    1. Give our new measure a name like Name of Days.
    2. Add the above DAX formula into the formula box.

    To see the results of our DAX formula, all we need to do is add it into the Values area of our PivotTable Fields window.

    This is very similar to the WEEKDAY function solution with Excel functions. The only difference is we need to aggregate the results with a CONCATENATEX function to display inside our pivot table.

    Get the Weekday Name in a Pivot Table with the FORMAT DAX Function

    Another DAX function we can use to get the weekday name is the FORMAT function. This is very similar to Excel’s TEXT function and will allow us to apply a custom format to our date values.

    The process is the exact same as the previous DAX example, but instead we create a measure with the above formula.

    Get the Weekday Name with a Power Pivot Calculated Column

    If you have the power pivot add-in for Excel, then you can use DAX to create a calculated column in the data model.

    If our data isn’t already in the data model, we can easily add it by going to the Power Pivot tab in the ribbon ➜ selecting Add to Data Model.

    Now we can open up the power pivot window by going to the Power Pivot tab ➜ selecting Manage Data Model.

    Inside the power pivot window, we can add our new calculated column.

    1. Double click on the Add Column heading and give the new column a name like Weekday.
    2. Select a cell inside the new column and add the above DAX formula into the formula bar and press enter.

    When we close the power pivot window, we now have the new Weekday field available to use in our pivot table and we can add it into the Rows, Columns or Filter area of the pivot table.

    Conclusions

    There are lots of options to get the name of the day from a date in Excel.

    We covered formatting, Excel formulas, power query and DAX formulas in the data model.

    There are probably a few more ways as well. Let me know in the comments if I missed your favourite method.

    Excel Date Formula Examples

    How to show month name or weekday for a date, first or last day of the month, week start date, subtracting dates, and more Excel date formulas. Get the free workbook with examples.

    There are specialized date formulas on these pages: Leap Year Calculations, Easter Date Calculations, and see how to find the Nth Weekday in Month (e.g. the 4th Thursday)

    And if your dates won’t change format, see the Date Format Troubleshooting Tips page.

    Calculate Number of Days

    To calculate the number of days between two specific dates, you don’t need a special function — just subtract the earlier date from the later date.

    Excel dates are really stored as numbers, so you can subtract one date number from another date number.

    However, the result cannot be a negative number, so subtract the earlier date (smaller number) from the later date (bigger number).

    Subtract Dates in Excel

    In this example, there is a January start date in cell A2 and a June end date in cell B2.

    To find the number of dates between the start date and end date, use this formula in cell C2:

    • =B2-A2

    Tip: If the result looks like a date, instead of a number, change the cell’s number format to General

    subtract start date from end date

    Problem When Subtracting Dates

    Sometimes when you’re working with date functions, the formula cell shows all number signs (hash tags, pound signs) instead of a number or date.

    That happens if the result is a negative number — Excel dates must be zero or greater. Excel dates cannot be negative numbers.

    In this example, the formula cell shows all number signs if you subtract the later date (B4) from the earlier date (A4). The result is a negative number, and Excel cannot display the result

    error when subtracting end date from start date

    Get the Current Date

    To calculate the the current date, use the TODAY function. It will automatically update, if you open the workbook on a different day.

    NOTE: To enter the current date as a static value, that will not update, use the shortcut key: Ctrl + ;

    Get Year, Month, Week or Day Number

    If a cell contains a date and time, you can use the following functions to extract the year, month number, or day number from the date. In this example, the date is in cell A2.

    Year: =YEAR(A2)

    Month: =MONTH(A2)

    Week: = WEEKNUM(A2)

    Day: = DAY(A2)

    NOTE: If the result looks like a date, instead of a number, change the cell’s number format to General

    year month and day numbers

    Get Month or Day Name

    If a cell contains a date, you can extract parts of that date as text. For example, show the month name, or the weekday name of the date. In this example, the date is in cell A2.

    Month Name (short): =TEXT(A2,»mmm»)

    Month Name (long): =TEXT(A2,»mmmm»)

    Weekday Name (short): =TEXT(A2,»ddd»)

    Weekday Name (long): =TEXT(A2,»dddd»)

    year month and day name

    Get End of Month Date

    If you need to calculate the first or last day of a month, the EOMONTH (end of month) function works well. In this example, the date is in cell B2.

    • NOTE: For Excel formulas to find the nth weekday in a month, such as the 3rd Tuesday, go to the Nth Weekday in Month page.

    The EOMONTH uses 2 arguments — Start Date, and Number of Months. To get a date in the current month, use zero as the number of months.

    Here are a few examples:

    —Last day of previous month: =EOMONTH(B2,-1)

    —First day of selected month: =EOMONTH(B2),-1)+1

    —Last day of selected month: =EOMONTH(B2,0)

    —First day of next month: =EOMONTH(B2,0)+1

    —Last day of selected month, last year: =EOMONTH(B2,-12)

    Or, combine the EOMONTH function with TODAY, to get dates relative to the current date. For example:

    —First day of current month: =EOMONTH(TODAY(),-1)+1

    year month and day name

    Get Date Value With INT

    If a cell contains a combined date and time, you can extract just the date value, by using the INT function. Excel stores dates as numbers, with the integer representing the date, and a decimal portion representing the time.

    The INT function returns just the integer portion of that number, which represents the date. In this example, the combined date/time is in cell A2.

    To get the date, enter the following formula in cell B2: =INT(A2)

    if function checks for empty cell

    Get Weekday Number

    To get the weekday number for a date, use the WEEKDAY function. For example, with a date in cell B4, this formula will show its weekday number (Sunday = 1, Monday = 2, etc.):

    weekday function

    WEEKDAY Return Type

    There is an optional second argument for the WEEKDAY function — return_type — which controls how the weekdays are numbered. The list of options is shown in the screen shot below.

    • If this argument is omitted, the default return_type of 1 is used, with weekdays numbered from Sunday (1) to Saturday (7)
    • In older versions of Excel, only options 1, 2 and 3 are available.

    week start date

    Get Week Number

    To get the week number for a date, use the WEEKNUM function. For example, with a date in cell B2, this formula will show its week number:

    weeknum function

    WEEKNUM Return Type

    There is an optional second argument for the WEEKNUM function — return_type — which controls how the weekdays are numbered. The list of options is shown in the screen shot below.

    week number return types

    • If this argument is omitted, the default return_type of 1 is used, with weeks starting on Sunday
    • In older versions of Excel, only options 1 and 2 are available.

    WEEKNUM Systems

    There are two systems used for the week numbers — System 1 and System 2. When you click on a Return Type in the list, you can see which system it uses.

    System 1 — Week containing January 1 is the first week of the year, and is numbered week 1.

    System 2 — Week containing first Thursday of year is first week of year, and is numbered as week 1. European week numbering system (ISO 8601)

    week number return type system

    Find Week Start Date

    To group data by week, use the WEEKDAY function to calculate a week start date for each record. In the screen shot below, there are dates in cells A2:A9. The first 7 dates are all in the same week, with a start date of Sunday, November 27. The final date is in the following week, which begins Sunday, December 4.

    Use this formula to calculate Sunday as the start date: =A2-WEEKDAY(A2+1,3)

    The "3" at the end of that formula tells Excel to use the numbers 0 — 6 for Monday to Sunday. The date is adjusted by one day, to subtract 0 if the date is a Sunday, 1 on Monday, etc.

    week start date

    Dates in GetPivotData formula

    If you’re using dates with the GETPIVOTDATA formula, you might have problems with date references. If the date format in the formula is not an exact match for the date format in the pivot table, the result might be an error.

    To help you avoid this problem, this short video shows a few Date workarounds for the GetPivotData function. There are more details and written steps on the GETPIVOTDATA page.

    Get Date From Text String

    Excel’s date functions won’t work if the value is a text string, instead of a real date. For example, imported data might have dates in this format — YYYYMMDD.

    To get the date from that string (it might be formatted as text or a number), you can use:

    Get Date with LEFT, MID, Right Functions

    The most common way to get the date from a YYYYMMDD string or number, is to use the DATE function, with LEFT, MID, and RIGHT extracting the year, month and day numbers.

    =DATE(LEFT(A2,4), MID(A2,5,2), RIGHT(A2,2))

    This video shows how to extract the year, month and day into separate columns, using these 3 formulas:

    • Year: =LEFT(A2,4)
    • Month: =MID(A2,5,2)
    • Day: =RIGHT(A2,2)

    Get Date with TEXT Function

    Instead of using the long LEFT, MID, RIGHT formula, UniMord shared a formula that’s much shorter.

    =—TEXT(A2, «0000-00-00»)

    In that short formula:

    • TEXT function formats the 8-digit YYYYMMDD number as «0000-00-00»
    • the 2 minus signs convert the result to a positive number.

    NOTE: This might not work for all regional settings, so test carefully before you use it in your workbooks. Unimord based this formula on a StackOverflow answer from Ron Rosenfeld.

    Get the Sample File

    To see how these formulas work, download the sample Date Functions workbook. The file is zipped, and is in xlsx file format

    Related Tutorials

    Don’t Miss Our Excel Tips

    Don’t miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.

    Excel Date Picker

    datepickercover.png

    This date picker adds a button to the ribbon, and optionally a couple more locations: the right click menu and a button in the grid when you select a date.

    Easy to use

    Quickly pick a date and update the cell value. Use the mouse wheel to move months quickly!

    Quick to customize

    From the ribbon you can launch the date picker, or quickly insert the date / time, and customize how you want the picker to work.

    How to install:

    After downloading and unzipping, put it in your xlstart folder which will load it when you start Excel. The folder location is different for each version of Excel, but is generally somewhere under:

    For Office 365 subscription the xlstart folder is generally here:

    alternatively:

    After unzipping, boot Excel and choose [ File -> Options -> Add-ins -> Go ] to manage Excel add-ins, then browse to the file you downloaded from here. People have had problems having it load each time when using the add-in UI, the xlstart folder seems more reliable.

    Afterwards:

    If you are running a more recent version of Excel, you may need to unblock the add-in after downloading due to Office security updates, and make sure you’re in a trusted folder.

    Q: Do you support 2003 and before?

    A: My original date picker works with Excel 2003 and before, though its no longer officially supported by me. Click here to download.

    Q: Does this work with 32 bit or 64 bit version of Excel?

    A: Recently updated to work in both 32 and 64 bit Excel.

    Q: I’m having trouble loading the add-in.

    A: You may need to unblock the add-in after downloading due to Office security updates, and make sure you’re in a trusted folder.

Ссылка на основную публикацию
Adblock
detector