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

The TEXT function changes the format of the numbers in a string

The TEXT function is very useful when you have to create a string which mixed text and number or date. You can customize each part of your date

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

Dates play a big role in many workbooks. With the tool conditional formatting you can create rules to highlight critical dates easily

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 Excluding the Weekend

Common error when adding days When you build a schedule for your team, you ususally don’t include the weekend. 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 you add …

Continue reading

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

Date Format in Excel

What is a date in Excel? In Excel, you can display the same date in many different ways just by changing the date format. Dates are whole numbers Usually when you insert a date in a cell it is displayed in the format dd/mm/yyyy. Now if you change the cell’s format to Standard, the cell …

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/