Tag Archive: Date format

Articles sur le thème : Date format

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/

Fill Series

The fill handle is very useful to copy formulas as you know but also, you can fill series of number or date. Look at the following tutorials to see what you can do. Fill series of numbers When you have to create a list of number, it is not necessary to create a formula or to …

Continue reading »

Permanent link to this article: https://www.excel-exercise.com/fill-series/

Difference between 2 dates - DATEDIF

Making a difference between 2 dates is not an easy task to do in Excel or with any software. 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? Suppose that you have the following …

Continue reading »

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

Calculation of tenths, hundredths and thousandths with Excel

3 Thousandths 😱😱😱 Looking at results of speed skating in the Olympic winter games of 2014, the gap between the first and second of the 1500m race was only 3 thousandths. Separate athletes with thousandth is extremely unusual and this give me the idea to calculate what represent 0.003 after 1500m race. With Excel, it's …

Continue reading »

Permanent link to this article: https://www.excel-exercise.com/calculation-tenths-hundredths-thousandths-excel/

Date format

It's important to understand how Date and Time are managed in Excel to custom their format. What is a date in Excel? Display number and not a Date. It's normal😉 In Excel, it is possible that a cell doesn't display a date as you expect but a number such as 42370. For a lot of …

Continue reading »

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

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 two extremely important functions. 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

A basic job with Excel is to easily isolate the weekends and the weekdays. This is the case when you hace to build an automatic calendar for example.  The function WEEKDAY Presentation This function returns a value between 1 and 7 corresponding to the value of the day in a week. =WEEKDAY(Date,Parameter) This function is …

Continue reading »

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

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/

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/

Convert Latitude and Longitude

Nowadays, GPS localization is in common usage. Some apps use decimal format (48.85833), others return the coordinates in degrees, minutes and seconds (48°51'29.99''). In this article I will show you how to convert one format to the other and vice-versa. How to write a coordinate in Excel GPS coordinates are generally written using the symbols ° (degrees), ' …

Continue reading »

Permanent link to this article: https://www.excel-exercise.com/convert-latitude-longitude/