DATE AND TIME Articles: Excel and Google Sheets

We often need to count holidays between two dates while developing a timeline for a project. In Excel, we can achieve this by using the COUNTIFS or the SUMPRODUCT function when we have a list of holidays’ dates in a range. This article will explain the process. Figure 1. Counting...

When we have two dates in two different cell references and wish to display them in one cell as date range as per our desired format then we will learn how to do that in this article. Using a formula based on the TEXT function and Ampersand (&), concatenating operator,...

We can use the EOMONTH and DAY functions to get the number of days in the month when we already have a single date. The steps below will walk through the process. Figure 1: Result of using the DAY and EOMONTH functions General Formula =DAY(EOMONTH(date,0)) Formula =DAY(EOMONTH(B5,0)) Setting up the...

We can use the MONTH and YEAR functions to test if two dates share the same month and year in Excel.  The following steps will walk through the process. Figure 1: Result of Date is the same month and year General Formula =MONTH(date1)&YEAR(date2)=MONTH(date1)&YEAR(date2) Formula =MONTH(B5)&YEAR(B5)=MONTH(C5)&YEAR(C5) Setting up the Data We...

We can use a set of formulas to calculate the pay associated with workers working overtime. The steps below will guide all levels of excel users through the process. Figure 1: How to Use Basic overtime calculation formula General Formula =(regular time*rate) + (overtime*rate*1.5) Formula Total pay for overtime: =(E4*G4)+(F4*G4*1.5)...

We can use the WORKDAY function to know if a day is a workday or not. This function calculates defined “workdays” dates in the future or past. It automatically excludes weekend dates and (optionally), holidays. The steps below will walk through the process. Figure 1: How to know that Date...

We can use the Excel YEARFRAC function to extract the number of days between two dates as a fraction of a specified year.  We will work through the process of using this function in the simple steps below. Figure 1: Result of the Excel YEARFRAC function General Formula =YEARFRAC(start_date, end_date,...

We can convert a date to month and year using two simple formulas. We will use the TEXT, MONTH and YEAR functions to perform this conversion. In the first example, we will use the TEXT function and in the second; we will use the MONTH and YEAR functions. Figure 1:...

Want to learn more about how to returns an integer representing the year of a supplied date using excel spreadsheet?? This post will give you an overview of how to returns an integer representing the year of a supplied date in excel. Syntax(Generic Formula) =YEAR(serial_number) Serial_number [Required]: It is the...

We can use the CHOOSE and WEEKDAY functions to make custom weekday abbreviations in any style we desire. We may want to, for example, format the days of the week to be abbreviated by the first letter or the first two letters. The steps below will walk through the process...