Tag Archive: Date in Excel

Articles sur le thème : Date in Excel

Difference between 2 dates – DATEDIF

Presentation Calculate the difference between 2 dates is not an easy task in Excel. You must take in consideration if you want to return a result based on the gap of the months or based of the years, or both. What is the problem? Let’s suppose that you have these 2 dates 28/07/2017 05/08/2017 The …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/difference-between-2-dates-datedif/

Example of Date, Week, Gap in days

This article presents all cases to calculate automatically a date from another date. All formulas are based for example on the current date TODAY () but of course all formulas work with any other date. Number of days in a month Excel allows you to calculate number of days in a month. Here you have …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/example-of-date-week-gap-in-days/

Title with date in Excel

Function TEXT converts any number into text and keep the specific number format. Presentation of the function TEXT TEXT function converts any number data into a text format according the parameters you give to the function. TEXT function has two parameters the number the format to apply Problem to solve Let’s take an example with …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/title-with-date-excel/

How to make automatic calendar in Excel

Creating a new calendar each month is a waste of time, but many people do as they think they need to rebuild it to get correct weekends and public holidays. But in fact, with few formulas, 2 conditional formatting rules, 1 dropdown list and 5 lines of VBA code, you can create an automatic calendar. …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/how-to-make-automatic-calendar-in-excel/

NOW & TODAY

The functions TODAY and NOW are 2 functions extremely important. They allow you to create dynamic workbooks using the system date on your computer. We will use these functions to create workbooks such as Automatic calendar Worker timesheet Follow-up of supplier payments Delivery delays … Presentation of the functions To view the current day, use …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/now-today/

Weekends or weekdays in Excel

Separating weekends and weekdays is a basic job in Excel. For example, when you have to build an automatic calendar.  The function WEEKDAY Presentation This function returns a value between 1 and 7 corresponding to the value of the day of the week. =WEEKDAY(Date,Parameter) This function is very, very, VERY useful for creating a test …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/weekends-weekdays-excel/

Using conditional formatting to highlight dates

Date functions in Excel make it is possible to perform date calculations, like addition or subtraction, resulting in automated or semi-automated worksheets. The NOW function, which calculates values based on the current date and time, is a great example of this. Taking this functionality a step further, when you mix date functions with conditional formatting, …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/using-conditional-formatting-highlight-dates/

Function DATE – YEAR – MONTH – DAY

Presentation of DAY, MONTH, YEAR The Functions YEAR, MONTH and DAY are very simple to understand. They extract each part of a date. DAY extracts the day of a date MONTH extracts the day of a date YEAR extracts the day of a date Individually, these functions do not interesting but used with the DATE function, they are …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/function-date-year-month-day/

Split Time and Date

If you have in a cell a time and a date (like the use of the function NOW) it’s easy to split them in 2 different cells. Split the date Let’s start from the following document where you have in column A data with date and time We want to split The date in column …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/split-time-and-date/

Add days without the weekend

Common error when adding days When you build a schedule for your team, you mustn’t include the weekend in your result. So you can’t write a formula like this =B2+C2 Look at the first results! 🧐🧐🧐 Even if the first result looks correct, it isn’t. The result is Monday but it should be Wednesday. If …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/add-days-without-weekend/

Date format in Excel

In Excel, you can display the same date in many different ways just by changing the date format What is a date in Excel? Dates are whole numbers Usually, when you insert a date in a cell, you have this result Now, if you change the cell’s format to Standard, the cell displays 42370 Explanation …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/date-format-in-excel/

Convert YYYYMMDD to DD/MM/YYYY

This article will explain how to convert date YYYYMMDD to DD/MM/YYYY. You can use one of two techniques: Convert with formulas With this technique, you must extract each part of a date with the text functions. So if you have a YYYYYMMDD format to transform, here are the steps to follow. Step 1: Extract the …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/convert-date-yyyymmdd-to-dd-mm-yyyy-without-formula/