Category: Date

Manipulate Date and Time is never easy but Excel can help you with formulas or specific techniques

Weekly calendar 2020 in one formula

Weekly calendar 2020 - All months

Discover how to create a weekly calendar and with just one formula.
With the new dynamic array, it’s very easy to do with the function SEQUENCE 🙂

Permanent link to this article: https://www.excel-exercise.com/weekly-calendar-2020-in-one-formula/

How to Highlight a Birthday Automatically

Gap in days and months before after the birthday

In this article, you will see how you can change color when a Birthday is coming up without using a macro.

Permanent link to this article: https://www.excel-exercise.com/how-to-highlight-birthday-automatically/

Convert YYYYMMDD to DD/MM/YYYY

Date convert in standard format without formula

You can transform your dates in format YYYYMMDD to DD/MM/YYYY without any formula.

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

How to display hours over 24

Excel displays time over 24H

By default in Excel, time are not displayed over 24H. But if you customize the default time format, you can display hours over 24H.

Permanent link to this article: https://www.excel-exercise.com/how-to-display-hours-over-24/

First day – Last day in Excel

On this page, you will find some formulas to calculate specific days automatically. Building specific dates, such as the first or last day, is always a very complex task, in Excel or any other software. The function TODAY has been used in all the examples but you can replace it with your own date value. …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/first-day-last-day-in-excel/

Time Formats in Excel

Standard time format in Excel

This article presents all the subtleties of the time format and the mistakes to avoid in Excel

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

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/

The Correct Value for a Week Number

The function WEEKNUM In Excel, to return the week number of a date you have the function WEEKNUM. =WEEKNUM(Date) Easy ? Sure ? 🙄🤔 Well in fact, it’s not so simple. It depends if you are in USA or in another country. The rule of calculation for the week is different between the USA and the …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/correct-value-week-number/

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/

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/