H2ECalculateAge

How to Calculate Age in Excel

Excel’s date and time functions make it easy to calculate the difference between two dates. And in this post, I’ll show you how you can calculate age in Excel. This can include a person’s age, or the interval between two dates. You can also break this difference into years, months, days, minutes, and seconds.

Use the YEARFRAC function to calculate the time in terms of fractions of years

One of the easiest ways to calculate age is by using the YEARFRAC function. As the name suggests, it will give you the fraction of a year. Suppose you wanted to calculate the difference between the start of the year 2000 and Christmas 2022. This is what your formula would look like:

=YEARFRAC("1/1/2000","12/25/2022")

Note that depending on your regional settings, you may need to enter date values in different formats. Alternatively, you could simply reference cells that contain date values so that you don’t need to do any hardcoding here.

The above formula will return a value of 22.983. Since Christmas falls towards near the end of the year, the number is close to 23. If instead you choose Jan. 31, 2022 as the end date, then the formula would return a value of 22.083.

Use the TODAY function to make your formula dynamic

To calculate age so that it is always going to be up until today’s date, you can use the TODAY function. This avoids you having to enter the current date each time you want an up-to-date calculation. For example, if you wanted to calculate the fractional years between the start of 2000 and today, your formula would look like this:

=YEARFRAC("1/1/2000",TODAY())

The TODAY value will automatically update so you don’t need to do anything to trigger that calculation. Just by opening your workbook, Excel will pull in the current date value, and your formulas that contain the TODAY function will adjust accordingly.

Calculating month, day, hour differences

If you want to calculate the difference in months rather than fractions of years, there’s an easy way you can do that as well. Excel has a DATEDIF function that can make that process quick and easy. The logic is the same as with the earlier formula, but the main difference is that you enter “m” for a third argument, indicating month. Here’s the formula, using the same values as earlier:

=DATEDIF("1/1/2000","1/31/2022","m")

This formula gives a result of 264, which equates to 22 years. You’ll notice the drawback here is there are no fractions or rounding, just 264 months. If I adjust the end date to the start of February (“2/1/2022”), then it will return a value of 265 months. Until the month is complete, the formula won’t add the extra month, even if you’re selecting a date that’s nearly at the end (e.g. January 31).

One alternative you can make is to calculate the difference in days:

=DATEDIF("1/1/2000","1/31/2022","d")

This formula will return a value of 8,066. If you were to divide this by 365, you would get 22.09863. That’s the same answer I would get using the YEARFRAC function if I entered the last (optional) argument in that function to specify that I wanted to use 365 days for my calculation (the default calculation uses 360).

DATEDIF doesn’t have an argument that lets you calculate hours or minutes. However, with the number of days, you can approximate that by multiplying by hours. If you did want to get to that precise level of detail, you would need to create a separate formula for hours and minutes — and you would also need to ensure your date values included that level of detail to avoid approximation.

Using the HOUR, MINUTE, and SECOND functions, you can subtract the starting date from the ending date to arrive at a difference for each of those time calculations.. For these types of details, you should reference the cells as opposed to key in the hour, minute, and second values to ensure everything is entered correctly.

Calculating date differences using the days, month, minutes, seconds, and yearfrac functions.

If you liked this post on How to Calculate Age in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

H2EnewTEXT

Excel’s New Text Functions Make Data Parsing a Breeze

Parsing data in Excel can be complicated, using a combination of functions ranging from LEFT, RIGHT, MID, and FIND. However, with the help of a few new functions that are available in Excel, the process is a whole lot easier for users. In this post, I’ll look at how you could parse out a date that is formatted as text using the new functions and comparing that with how you might have done it using the old functions.

In this example, I’m going to try parse out the numbers I need to convert the following value, which is reading as text:

This date is April 19, 2022. But because my regional settings are set to month/day/year this value doesn’t compute properly since it would be looking for a 19th month.

Pulling the day value (data before the delimiter)

The old method

The first number in the date value above relates to the day of the month. Using the LEFT function in Excel, you could use something like this:

=LEFT(X,2)

Where X is the cell value. That will pull the first two characters in the string. But in some cases there might only be one day for the date. And for that reason, I’m not going to hardcode the number of characters. The best approach (under the old method) is by using the FIND function to locate where the delimiter (“/”) is. The more versatile formula would look as follows:

=LEFT(X,FIND("/",X,1)-1)

The new method

One of Excel’s new text functions is called TEXTBEFORE. And as the name suggests, it will extract all the text that comes before a delimiter. Without needing the FIND function, I can simply do this to extract the day value:

 =TEXTBEFORE(X,"/")

Pulling the year value (data after the delimiter)

The old method

To grab the year in the date I could cheat and use the RIGHT function and just grab the last four numbers. But that wouldn’t be flexible enough in the event that I might have 2 digits instead of 4 as the year. This can get messy as now I have to use multiple FIND functions in order to determine the length. The key is to take the length of the function and subtract from that the position of the second delimiter. Here’s what that looks like:

=RIGHT(X,LEN(X)-FIND("/",X,FIND("/",X,1)+1))

The nested FIND functions can get a bit complicated. Here you’ll see even more efficiency with Excel’s new functions.

The new method

The TEXTAFTER function can greatly simplify this action because you can specify after which delimiter you want to pull the characters; there is no need to have nested functions with this:

=TEXTAFTER(X,"/",2)

In this formula, the characters after the second “/” will be extracted. Note: both the TEXTBEFORE and TEXTAFTER functions allow you to specify the instance of the delimiter (i.e. it doesn’t always need to be the first one).

Pulling the month value (data between delimiters)

The old method

The most challenging part of this process is undoubtedly to pull the data between delimiters. In this example, I’ll need to use the MID function and use nested FIND functions to determine the space in-between the delimiters. It’s an ugly formula if you don’t rely on hardcoding:

=MID(X,FIND("/",X,1)+1,FIND("/",X,FIND("/",X,1)+1)-FIND("/",X,1)-1)

That’s four FIND functions in one formula. You can quickly see how parsing out this information can be a challenge. But with the new Excel functions, it’s much easier to do this.

The new method

There isn’t a new function that specifically pulls the values between delimiters. But by using both the TEXTAFTER and TEXTBEFORE functions, you can do exactly that. Let’s start with just grabbing everything after the first delimiter:

TEXTAFTER(X,"/")

This will give us the following result: 4/2022. Obviously that’s not what I want. But now, I can nest this within the TEXTBEFORE function, and grab the value before that other “/” with the following formula:

=TEXTBEFORE(TEXTAFTER(X,"/"),"/")

We are still dealing with a nested function here, but this is no doubt easier than all those FIND functions under the old method.

Using an array function

Another option that you can use is to extract all the values between the delimiters using the TEXTSPLIT function. Simply enter the following formula:

=TEXTSPLIT(X,"/")

Then the values will be extracted into three cells, one for the day, month, and year:

The benefit of this approach is you can quickly pull everything from the cell you’re parsing data from.

Regardless of which option you choose, Excel has given its users some new tools that can make the parsing much easier and less complicated than it was before.


If you liked this post on Excel’s New Text Functions, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

H2EDayofWeek

Calculate the First Day and Last Day of the Week

Excel’s WEEKNUM function can return the specific week that a date falls in. But to do the reverse is a bit more challenging. In this post, I’ll show you how you can get the first and last day of a week (as well as anything in-between).

Setting up some variables

You can make this into a large and complex formula, but I’m going to make it a bit more organized by utilizing named ranges. The two names ranges I’m going to set up are for the day of the week (DAYNUMBER) that I want to calculate for, and the first day of the year (FIRSTDAY).

I’m going to use Monday as the day of the week my week starts on. On my regional settings, that is weekday #2. If you’re not sure about yours, you can use the WEEKDAY function on a day that is a Monday (or whichever day you wish to use) to determine the number associated with that.

Calculating the difference between the first day and your desired day of the week

The day the year begins on serves as an important starting point. This year began on a Saturday. If my desired day is Monday, then I need to calculate the difference between those days of the week. The formula for that would be as follows:

=DAYNUMBER - WEEKDAY(FIRSTDAY)

This returns a value of -5. If I wanted to know when the first Monday of the year was, I couldn’t just deduct 5 from the first day or I’d end up in the wrong year. What I need to do is to set up an IF function to say that if the difference is negative, I will add 7 to adjust for that fact. And if it isn’t negative, then I can just add to the starting date. Here is my formula thus far:

=IF(DAYNUMBER-WEEKDAY(FIRSTDAY)<0,DAYNUMBER-WEEKDAY(FIRSTDAY)+7,DAYNUMBER-WEEKDAY(FIRSTDAY))

To get to the right day, I need to add this to my starting date:

=FIRSTDAY+IF(DAYNUMBER-WEEKDAY(FIRSTDAY)<0,DAYNUMBER-WEEKDAY(FIRSTDAY)+7,DAYNUMBER-WEEKDAY(FIRSTDAY))

Using the above formula, Excel tells me that Jan. 3, 2022, was the first Monday of the year, which is correct. But I need to adjust the formula to ensure the calculation puts me in the correct week.

Adjusting for the week number

The above formula works if I want the first week. If I want it to be more flexible than that, I need to include the week number in my calculation. For that, I’m going to create a named range called WEEK. The key is in adjusting the +7 calculation. In the first argument of my formula, when it was negative, I added 7. If I want the second week, then I need to add it by another factor of 7. Here’s how that part of the formula would look:

WEEK-WEEKDAY(FIRSTDAY)+(7*WEEK)

I also need to add that part to the second argument, which currently doesn’t adjust for the week number:

WEEKDAY(FIRSTDAY)+(7*(WEEK-1))

The completed formula is as follows:

=FIRSTDAY+IF(DAYNUMBER-WEEKDAY(FIRSTDAY)<0,DAYNUMBER-WEEKDAY(FIRSTDAY)+(7*WEEK),DAYNUMBER-WEEKDAY(FIRSTDAY)+(7*(WEEK-1)))

Now I can adjust the calculation for different days of the week and different week numbers. And so whether you’re looking at the first day of the week or the last day of the week, you can just adjust the day number you’re looking for.

Here’s what the formula would look like without named ranges if the year was the current year and it was pulling the Monday of the 50th week of the year:

=(DATE(YEAR(NOW()),1,1)+IF(2-WEEKDAY(DATE(YEAR(NOW()),1,1))<0,2-WEEKDAY(DATE(YEAR(NOW()),1,1))+(7*50),2-WEEKDAY(DATE(YEAR(NOW()),1,1))+(7*(50-1))))

If you liked this post on How to Calculate the First Day and Last Day of the Week, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

H2EconvertMonthNumber

How to Convert Month Number to Month Name in Excel

Do you have a report in Excel that lists the months as the numbers 1 through 12 and you want to convert that into the actual month names? Below, I’ll show you how you convert a month number into a month name in Excel.

Here’s an example of data that shows monthly sales but it only lists the number as opposed to the name:

Sales by month with the month number showing in digits.

If you had the entire date in a cell you could format it so that it showed the month. For instance, what I could do is type in =TEXT(A1,”MMM”) which would convert the value in cell A1 into a three-letter abbreviation for the month. But the numbers 1 through 12 will return values of “Jan” as Excel will think that you are referring to the first month of the year.

However, that changes once you get to the number 32. Since there are only 31 days in January, the number 32 will return a value of “Feb” if you were to continue on with that formula. And so the trick is to multiply these values all by a factor of 28. Since that’s the minimum number of days every month will have, it ensures that jumping by 28 each time will put you into each month of the year. This is what my values will look like:

Month numbers multiplied by 28.

To prove this out, here is which dates those days of the year would correspond to:

Day of the year along with the corresponding date.

In month 12, we barely make it in December using this approach but that’s good enough. And even in a leap year, multiplying by 28 still works. In this example, I include 2024, the next year that February gets an extra day:

Day of the year along with the corresponding date, including a leap year.

So now that we’ve confirmed that those numbers will fall within the correct months, we can use the TEXT formula noted above to convert those numbers into month dates, and this is what we end up with:

Month numbers converted into month names.

You can also multiply by 29 and this logic will still work. But if you use 27 then your months will be wrong by the time you hit September and if you use a multiple of 30, then in non-leap years you will be jumping too quickly and you will have two dates in March.


If you liked this post on How to Convert Month Number to Month Name in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

H2Edatepicker9001

Free Excel Date Picker Add-In

Are you looking for an easy way to add a date to your Excel spreadsheet? You can download my free date picker add-in for Excel. It is useful if you have a form and you want people to select dates or if you just want an easy way to enter a date without worrying about whether it is in the right format.

***Please note on an earlier version of this add-in (and as reflected in the video), the calendar was designed to pop up next to the active cell. However, due to many issues related to scrolling and possible zooming, and multiple screens, it is now set to open at the top (and in the middle) of the screen***

How the date picker add-in works

To launch the add-in, click on CTRL+SHIFT+Z, which will trigger the following calendar to pop up:

Date picker add-in for Excel.

By default, it will jump to the current month. Clicking on any of the dates will enter the date value into the active cell. You can use the arrow keys on the left or right side to change the months. If you want to jump by years, double-click on the year and just enter the desired year. The calendar will automatically adjust, which will be quicker than if you were to just continue pressing the arrow buttons.

Right now the add-in is a stand-alone but look for it to be included as part of a larger add-in package. If you have any suggestions for other features to include in an add-in, feel free to contact us.

How to install an add-in

You can download the date picker add-in here. Once you’ve saved it, go into Excel and select File -> Options -> Add-ins and then depending on your version, you may see an option at the bottom to go to manager Excel Add-ins:

Manage Excel add-ins button.

Click on the Go button and then you will have a list of add-ins you can install. If you didn’t save the add-in into the default folder where the rest of the Excel add-ins are, you just click the Browse button to find where you saved the file. Then, make sure the add-in is checked off and click OK and it will be ready to go.


If you liked the Date Picker Add-in, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

Optimized-timecalc

Calculate Hours and Minutes Between Two Times

Do you want to know how to calculate how many hours and minutes have elapsed between two times? Below, I’ll show you how to do that with Excel formulas. The difference won’t be in just fractions of days or hours but real minutes and hours that make it easy to measure. To make this work, however, the first step is entering in the time correctly.

How to enter time in Excel

Excel has a built-in time function called TIME where you can enter the hour, minute, and second. For example, if you wanted to enter a time of 6:30 AM, you could enter a formula of TIME(6,30,0). Alternatively, you could type in 6:30 AM — the key is leaving the space between the time and the AM/PM indicator.

However, the one clear limitation here is that this won’t help you if you want to calculate the hours and minutes between times that span more than just one day. By using the TIME function, or entering just the hours and minutes, Excel is always going to assume you’re talking about the current day.

The best way to enter in time is to also factor in the date. Depending on your regional settings you might enter this differently, but this is how I’d enter a time of 8:00 PM on Nov. 20 on my computer:

2020-11-20 8:00 PM

I can also use a 24-hour clock and type in the following:

2020-11-20 20:00

Either way, Excel knows what time I’m talking about. If you always want to return the current time, you can use the NOW function.

For the start time, I’ll set it to the start of the year: 2020-01-01 0:00

Calculating the difference

Just using the minus operator, I can get the difference between these two dates as follows:

Calculating the difference between two times.

By default, Excel will return the number of days, including the fractional days as well. To convert days and calculate the hours instead, we will multiply the difference by 24, since that’s how many hours are in a day. That gives us the following:

Time difference in hours.

That’s 7,796 hours between those two dates and times. It’s a nice round number but what if we changed it so that the end date was at 8:30 pm, or 20:30, this is what the updated calculation would look like:

Time difference in hours.

Now I’ve got that residual 0.50 which indicates half an hour. But I want minutes, not fractions of an hour. The easiest way to do this is to create one calculation for hours, and another for minutes. Then, afterwards, you can concatenate them together. To get the total hours, I’ll adjust my formula to include the ROUNDDOWN function so that it does not include the 0.5. It looks something like this:

=ROUNDDOWN(datedifference*24,0)

Where datedifference is that raw calculation between the two dates and times. In my calculation, I’m still multiplying the time difference by 24 to get to hours, and then I round that to 0 spots, which is indicated by the 0 in the second argument. Now it will only show 7,796.00 for hours.

To calculate the number of minutes, I’ll need to multiply the datedifference by 24 and then again by 60, to convert the difference into minutes. This is what my calculations look like thus far:

Time difference in hours and minutes.

My hours are nicely rounded but my minutes include the total minutes, which is not what I want. I only want the minutes that are left over after the hours are factored out. Here I can make use of the MOD function which will tell me the remainder after division. I’ll adjust the minute calculation to calculate the remainder after I’ve divided the total minutes by 60. This will determine what’s left over after pulling out full 60-minute hours, which is that residual 0.50 that I’m after. Here’s what this formula looks like:

=MOD(datedifference*24*60,60)

That gives me the following result for minutes:

Time difference in hours and minutes.

Now I get a nice and round 30 minutes. There is the potential that I can also get partial minutes if I have seconds in my calculation. This could be the case if I’m using the NOW function. To correct for this, I can again use the ROUNDDOWN function as I did for hours.

However, let’s assume that you also want to track seconds. We can do that as well. I’ll break out another column for seconds. There, I’ll multiply the difference by another factor of 60, to get the following:

Time difference in hours and minutes and seconds.

I added 25 seconds to my end date, which is why you’ll notice there’s a slight change in the difference column from this screenshot and the earlier one. Right now, total seconds tells me there are 28,067,425 seconds between these two times. If I want to get the raw number of seconds, then I’ll again use the MOD function and again use 60 as a divisor, since now I want to factor out the minutes:

Time difference in hours and minutes and seconds.

I now have a clean breakdown between hours, minutes, and seconds between these two times. But if you want to calculate more than just hours between two times, you can also incorporate the number of days as well.

Breakdown of days, hours, minutes, and seconds

If I wanted to take a different approach and break the difference down by days, and then by hours, minutes, and seconds, I’ll first need to break out the days. Since that’s the default calculation for Excel, all I need to do is use the ROUNDDOWN function on the difference. The formula is as follows:

=ROUNDDOWN(difference,0)

And that gives me this:

Time difference in days.

If I wanted to get the hours that are remaining, what I can do is take the difference, use the MOD function, but this time I’m using a divisor of just 1, since I really only want the decimal place after the full number. Then I’ll multiply that by 24 hours, and again, use the ROUNDDOWN function:

=ROUNDDOWN(MOD(difference,1)*24,0)

Now my hours total looks like this:

Time difference in days and hours.

I’ve got a nice round 20 hours, which makes sense since 8:00 PM is 20:00 on a 24-hour clock. To calculate the difference in minutes, I can revert back to the earlier calculation where I used the MOD function to determine what’s left over after multiplying the difference by 24 and 60, and then dividing it by 60 minutes:

Time difference in days, hours and minutes.

The seconds calculation will work the same way as well. The only difference in the way to break out hours and days was to adjust the hours calculation to ensure it isn’t taking in the full hour difference, only the residual amount that pertained to the current day.

Now that you’ve got all the chunks broken down between days, hours, minutes, and seconds, you could concatenate that into one large formula, Something like this might work:

=CONCATENATE(daydifference,” Days “,hourdifference,” Hours “,minutedifference,” Minutes “,seconddifference, ” Seconds”)

That produces the following:

Total time difference in days, hours, minutes, and seconds.

For this not to be a messy result, you’ll want to ensure you’re using the ROUNDDOWN function in each of those calculations so that you aren’t keeping any trailing numbers.


If you liked this post on how to calculate hours between two times, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

countdowntimer

How to Make a Countdown Timer in Excel

A countdown timer can help you track how much time there’s left to do a task or until a deadline comes due. Below, I’ll show you how you can make a countdown timer in Excel that can track days, hours, minutes, and seconds. In order to make it work, we’ll need to use some VBA code, but it won’t be much. And if all else fails, you can just download my free template at the end of the post and repurpose it for your needs.

Let’s get right into it and start with the first step:

Calculating the difference in days,

To calculate the difference between two dates is easy, as all you’re doing is subtracting the current date and time from when you’re counting down to.

The start date is just going to be today, right this very second. And Excel has a convenient function just for that, called NOW. It doesn’t require any arguments and all you need to do is enter the following formula:

=NOW()

Entering the date and time you’re counting down to is a bit trickier. As long as you enter it correctly, then calculating the differences will be a breeze. However, this may involve a little bit of trial and error since it’ll depend on how your regional settings are setup. For the countdown date, I’m going to set it to the end of the year. Let’s say 11:00 PM on New Year’s Eve. Here’s how I input that into my spreadsheet:

2020-12-11 11:00 PM

The key things to remember here are that there should be a space between the time and the AM/PM indicator (if you use it) and there should be two spaces between the date and the time. Then, it’s just a matter of whether you’ve got the right order of date, month, and year. This is where you may need to do some testing on your end to ensure you’ve got the correct order.

Now that the dates are set up, we can calculate the difference in days. To do this, we can just calculate the difference and use the ROUNDDOWN function to ensure we aren’t adding partial days:

There are 222 days left until the end of the year. By using the NOW function, the formula will automatically update and tomorrow the days remaining will change to 221, and so on. If your output’s looking a little different, make sure to check the formatting and that it’s set to days.

Calculating the difference in hours, minutes, and seconds

There’s not a whole lot of complexity when it comes to calculating the difference in hours, minutes, or seconds. We’re still subtracting the current date from the deadline. The only difference is that now we’re just going to change the formatting. If I do a simple subtraction, I end up with a fraction, which isn’t really usable in its current format:

Counting down the hours, minutes, and seconds left.

The trick here is to change the format of this cell so that it shows me hours, minutes, and seconds. And that’s an easy fix. If I just click on cell C10 and click CTRL+1, this will get me to the Format Cells menu. In here, I’ll want to select a Custom format so that the cells just shows hours, minutes ,and seconds:

Applying a custom format.

Here’s what the countdown timer looks like after the format changes:

Countdown timer.

It’s important to include a date in the calculation even though we’re just doing a difference between hours, minutes, and seconds. Otherwise, the formula wouldn’t correctly calculate in all situations, such as when the deadline hour is earlier than our current hour.

Putting it all together

Now that all the calculations are entered in, now it’s just a matter of formatting the data. We can create a countdown clock that separates days remaining, from hours, minutes, and seconds remaining.

One cell can have the difference in days, while another will have the difference in hours, minutes, and seconds. This goes back to just modifying the formatting and applying a custom format. Here’s how mine looks:

Full countdown timer.

Although we’ve gotten to this point, the challenge is that this countdown timer still doesn’t update on its own. Unless you want to click on the delete button all the time, the countdown isn’t going to move unless there’s something to trigger a calculation in Excel. That’s why we’re going to need to add a macro to help us do that, which bring us to the important last step of this process:

Adding a macro to refresh every second

We need a macro to update the file. Whether it’s every second, every five seconds, it’s up to you. While the countdown timer will update when someone enters data or does something in Excel, that’s not much of a countdown. This is where VBA can help us. If you’re not familiar with VBA, don’t worry, you can just follow the steps below and copy the code.

To get into VBA, click on ALT+F11. From the menu. Once you’re there, click on the Insert button on the menu and select Module:

Creating a new module in VBA.

Over to the right, you’ll see some blank space where you can enter in some code. Copy and paste the following there:

Sub RunTimer()

    If Range("C10") <> 0 Then
        Interval = Now + TimeValue("00:00:01")
        Application.Calculate
        Application.OnTime Interval, "RunTimer"

    End If
End Sub

One thing you may to change is the reference I made to cell C10. Change that to where you have your countdown timer. As long as there’s a value in the cell, the macro will continue running. All it does is check if there’s a value there, and if there is, it updates the worksheet every second. And by doing that calculation, your countdown timer will update even if you’re not making any changes to the spreadsheet.

You can also change the interval which currently updates every second, as noted by the 00:00:01. You can change this to five seconds, 10 seconds, however often you want it to update.

But there still needs to be something that triggers the macro to start running. You can assign a button or shortcut key to do that.

However, in this example I’ll activate it when the sheet is selected. Inside VBA, you should see a list of worksheets. Double-click on the one that contains your countdown timer:

Worksheets in VBA.

You’ll again see blank space to the right where you can enter code. And you’ll also see a couple of drop-downs near the top that you’ll want to look for. By default, the first one should say (General). Change this to Worksheet:

Selecting the Worksheet object.

Next, change the other drop-down which will probably say SelectionChange. Change it to Activate. Then you should see something like this:

Selecting the worksheet activate event in VBA.

Copy the following code into there to call the macro we created above:

RunTimer

Now when you switch to another worksheet and come back to the current one you’ll notice your countdown timer is updating on its own. If you want it to stop it, just clear the cell that has the timer. Otherwise, the macro will continue running every second.

The Countdown Timer Template

If you’d rather just use a template, then you can download one that I’ve made here. You don’t have to worry about macros and instead you just need to enter the end time; the time that you’re counting down towards.

I’ve also got a start/stop button that you can toggle to get the countdown timer going and that will pause it:

Countdown timer.

You can move the button as well as the time your counting down to onto another sheet if you don’t want someone altering it. If you have any questions or comments about this template, please send me an email at contact@howtoexcel.net


If you liked this post on how to make a countdown timer in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

calendar-309912_640

How to Do a Weekly Sales Analysis and Compare the Same Days of the Week

Whether you’re doing a forecast or looking back at how your sales were over a period of time, it’s important to ensure that you’re comparing apples to apples. While monthly and yearly numbers won’t have too much noise, once you’re trying to do a daily or weekly sales analysis, that’s when things can get a little challenging.

Below, I’ll show you how you can do a weekly sales analysis where you’re comparing the same days of the week against one another. This will give you an accurate picture of your year-over-year performance.

Step one: determine which day of the week you want to start on

This is a simple step and you’re probably going to go with either Sunday or Monday. But it’s an important one to consider because when you’re looking at weekly sales numbers, you want to be consistent. And while you can refer to the week number when comparing one week to a previous year, saying week 32 is not going to be as useful as saying the week starting August 5 or ending August 11.

In my example, I’m going to use Monday as my starting point to ensure that I’m not breaking up the weekend (the default in Excel is Sunday). To make it easy to compare a week, it will be helpful to create a header for the days of the week so it looks like a calendar.

Step two: entering the first date of the weekday you selected

The first Monday of 2020, wasn’t until Jan. 6 this year, which would be the second week of the year if we start on Mondays. The previous Monday was Dec. 30, which was technically week 53. Weeks 1 and 53 are often abbreviated. For now, just accept that there’s no Monday in Week 1 of 2020. I’ll show you how we can get around this problem further down.

For now, Jan. 6 will be our starting point which we’ll call Week 2. Now, that we have our starting point, we can build out what our subsequent weeks will look like.

For example, if I want to find out the start date for week 40, what I can do is simply use the following formula:

weekly sales analysis dates

First, I multiply 7 by the difference in weeks. Then, add that to the first Monday value. In this example, it tells me the 40th Monday of the year is Sep 28, 2020. That’s why setting up the first Monday values is important to ensure that it’s easy to get the remaining dates.

This is the easier approach to take. However, later on I’ll show you a way where you don’t have to enter in the first Monday of the year.

Step three: filling in the remaining dates of the week for your sales analysis

Getting the starting date of the week is the toughest part. From there, all you have to add is just add 1 to each subsequent day:

weekly sales analysis dates

Just adding 1 to the previous date will increment to the next day. No special formulas needed here.

Step four: getting the prior-year date

To get the previous year’s data you can follow the same approach as in step two. However, I’ll use this as an opportunity to show you another way that you can get the data. One that won’t require you to pull out the calendar.

First, we need to know what day of the week Jan. 1, 2019 fell on. To do this, we can just use the following formula:

=WEEKDAY(“Jan 1, 2019”,2)

The reason I put the number 2 as the second argument is because my week is starting on a Monday. If I set it to 1 or left it blank, the default would be Sunday. This is important because if Monday is my first day of the week then it’s day value is 1 and Sunday is 7. Had I used Sunday, then Sunday would have a value of 1 and Monday would be 2. This is why it’s important to know which day of the week you want your week to start on.

In 2019, Jan. 1 fell on a Tuesday, and the formula above gave me the result of 2. (Monday is 1, so Tuesday would be 2). The reason I need to know the weekday is because I need to adjust the date to find out when that week actually started. I use the following formula to do that:

=DATEVALUE(“Jan 1, 2019”)-(WEEKDAY(DATEVALUE(“Jan 1, 2019”),2)-1)

What this formula does is subtracts Jan 1, 2019 from the number of days it is above day 1. It then moves the date back. I can simplify this formula by entering Jan 1. 2019 in cell A1. Then my formula looks like this:

=A1-(WEEKDAY(A1,2)-1))

I no longer need to use the DATEVALUE function and now it’s a bit easier to use. There’s also less chance of an error when entering the date. Now, when I want to find out the first day of the week, I can multiply 7 times the week number and add to this calculation:

=(A1-(WEEKDAY(A1,2)-1))+(7*(B1-1))

B1 is the week number. In this example, if I were to enter Jan 1. 2019 for cell A1, that would give me a result of Dec 31. 2018 for the start of Week 1. Excel also considers this to be the week that contains Week 53 and Week 1. This is where you can get around this issue. By calling this Week 1 of the current year and including December’s days into this week, it will ensure you don’t have the Week 53 problem. It may not look great to call the previous year’s dates part of the new year but it avoids having to manually make adjustments for this period.

Using the updated formula, I can change the Jan. 1 date to reflect 2019 and use week 40 to update my comparables for the weekly sales analysis:

weekly sales analysis dates

From here, it’s just a matter of now using a SUMIF function on your data to pull the sales for each one of these dates and you’ve got your comparable sales numbers. With 2020 being a leap year, you can see that the dates have moved up two days from the prior year. Without the date adjustment, you could have ended up comparing a Sunday (Oct 4, 2020) against a Friday (Oct 4, 2020).


If you liked this post on how to do a weekly sales analysis, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

date2

How to Remove Time From Excel Date

If your data contains date and time, and you only need the former, there are ways for you to remove time from the excel date. The first step, however, is in determining whether your data is in date format or whether the information is stored as text. Depending on which one it is, it will change how you will need to manipulate the data.

You can use the TYPE function to determine whether your data is in text or numeric format. The function evaluates a value and if it is numeric it will return 1 and if it is text the result will be 2. That will determine which path you need to focus on: converting a text date or just pulling the date values that you need. The latter is the easier of the two approaches.

Removing time from a date value

If the data is in date format, then it’s as easy as using the DATE function to pull out the fields you need. Let’s start with a date that shows the following:

2020-02-29 12:00:00 PM

It has more detail than we need with the time in there but it also has everything that’s needed—year, month, and day. The easiest way to pull out the date is using a formula as follows (where A1 is where the original data is):

=DATE(YEAR(A1),MONTH(A1),DAY(A1))

The DATE function takes three arguments: year, month, and day. By pulling these values out from the cell that has the time, we’re effectively creating a new value that has everything except the time. Now, if you don’t want this to remain a formula what you can do is copy the cell with the date and not the time, and paste it as values. Now, you’re left with hard-coded date values that do not contain the time.

As mentioned, this is the easy part of the process. The more difficult one is if your date is stored as text and where the DATE function results in an error if you try the above calculation. Let’s take a look at how to remove time from an Excel date when it’s in text format.

How to extract the date from a text field

If the same value above was stored as text, the formula involving the DATE function would result in an error. To pull the values that are needed to arrive at a proper date value, we’ll need to parse the data. Parsing can be a bit complicated but when you’re dealing with text, it’s the only way around getting the data you need.

In the above example, the date fields were separated by hyphens but it could be that slashes are used as well. Ultimately, it doesn’t matter, so long as there is some pattern that separates the month, day, and year fields. We will still use the DATE function. But in order to put the correct values in, pulling out the key information is going to be the challenging part.

Let’s start with pulling out the month, since in a month-day-year format, it’ll be the first value and thus, the easiest to extract. Here’s how the formula to pull the month would look, again, assuming A1 is where the data is:

=LEFT(A1,FIND(“-“,A1,1)-1)

Since the month is the first value, we use the LEFT function to pull the characters at the beginning of the cell. A1 is the cell we’re looking at, and the second argument is the length of the string to pull. Here, we’re looking for the dash(-) within the cell and subtracting one character so that the dash itself isn’t included in the extraction. This formula would produce a value of ’02’ and correctly return the month value.

To get the day is a bit trickier since it’s between dashes. It’s still possible to extract it but the formula is a bit more complex and requires using the MID function. Here’s the function with just the first two arguments filled in:

=MID(A1,FIND(“-“, A1 )+1,

The first part of the formula specifies the starting point. For here, we’re again using cell A1 but this time we’re looking for the dash using the FIND function to indicate where the second value begins. We add one to this value to ensure that we aren’t starting at the dash. Here’s what the next argument looks like, for the length of the value:

=FIND(“-“,A1,FIND(“-“, A1,1)+1)-FIND(“-“, A1,1)-1)

Here what we’re doing is using the FIND function to search for the dash but this time we aren’t starting from the first position but are starting from where the first dash was found, and adding a one to that. Then we subtract where the first dash was found, and the difference is the length of the string. It’s a complicated, nested function but it does what we need it to do. The completed formula for the day looks as follows:

=MID(A1,FIND(“-“,A1,1)+1,FIND(“-“,A1,FIND(“-“,A1,1)+1)-FIND(“-“,A1,1)-1))

The last part is to extract the year. And because this comes after the second dash, we’re going to need to nest two FIND functions, not just one. You could try and always start from a certain number, for example, the seventh character if your date format will always by mm-dd-yyyy. However, using the FIND function ensures you aren’t taking any assumptions (e.g. they may be leading spaces). I also avoid hardcoding numbers in formulas whenever possible. Here is the formula that remains for the year function:

=MID(A1,FIND(“-“,A1,FIND(“-“,A1,FIND(“-“,A1,1)+1))+1,4)

The nested FIND functions are needed to ensure that I’m starting to search for the dash after the second instance was found. I use the number four for the last argument because rather than making this formula even more complicated, I figure the year will either be two characters or four, and it won’t deviate. If your data contains two characters for the year, then you can just change the final argument accordingly.

That leaves us with this long formula to extract the date for the mm-dd-yyyy:

=DATE(MID(A1,FIND(“-“,A1,FIND(“-“,A1,FIND(“-“,A1,1)+1))+1,4),LEFT(A1,FIND(“-“,A1,1)-1),MID(A1,FIND(“-“,A1,1)+1,FIND(“-“,A1,FIND(“-“,A1,1)+1)-FIND(“-“,A1,1)-1))

It’s a complicated one so it may be easier to just copy and paste it rather than trying to reconstruct it yourself. If your original date is in dd-mm-yyyy format, here is a formula for that:

=DATE(MID(A1,FIND(“-“,A1,FIND(“-“,A1,FIND(“-“,A1,1)+1))+1,4),MID(A1,FIND(“-“,A1,1)+1,FIND(“-“,A1,FIND(“-“,A1,1)+1)-FIND(“-“,A1,1)-1),LEFT(A1,FIND(“-“,A1,1)-1))

This just involves flpping around the formulas to grab the month and day. If your dates use “/” instead of “-“, then you can just to a find and replace in the formulas above to replace all the “-” with “/” or whatever else your system may use. Regardless how the data is separated, you can adapt the formula to how your data looks.

As you can see, having your data in the right format can make this process a whole lot easier. It’s once you get into text that it becomes much more challenging in pulling the date out. And again, once you’ve got the data you want, copying and pasting as values will ensure you don’t have to keep both the old and new data together.


If you liked this post on how to remove time from Excel date, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

pocket watch sand

How to Calculate Time Difference in Excel

If you need to keep track of time entries, whether for a timesheet or some other purposes, it’s important you know how to calculate time differences in Excel, and that’s what I’ll show you how to do in this post. If you’re just looking for the difference in dates, including months, days, or years, then refer to this post. But if you need time differences, keep reading.

Entering time correctly

In order for the difference in time to be calculated correctly, it should ideally be entered in the right format to start with. Let’s use the example calculating the hours worked for a regular 9-to-5 shift.

You can use a 24-hour clock or AM/PM to enter time. If you’re using AM/PM, then you’ll want to make sure you enter a space between the time and the AM/PM indicator. For example: 9:00 AM rather than 9:00AM.

If you enter it correctly, then the value you just entered should align to the right. This means that Excel has interpreted it as time. You can also tell because in Excel it will show as 9:00:00 AM in the cell details. Normally, you aren’t going to enter seconds but Excel will track those extra zeroes anyway.

If you were to just enter 9AM then you’ll notice after hitting enter that nothing happens and Excel doesn’t align it to the right side of the cell. This means that what you’ve entered Excel is reading as text.

That is a problem because if you want to calculate the time difference correctly, it’ll get a whole lot more complex and require using IF and MID functions. It’s not impossible (and I’ll show you how to do so later on), but it will make it a lot more complicated.

Calculating the time difference

Now that you’ve made sure that the time entries are entered correctly, it’s time to show you how to calculate time difference in Excel. Here’s how it looks if I just take the end time and subtract the start time:

calculating time difference for am pm and 24-hour time

If you’re using the 24-hour clock then it looks okay. However, the calculations are still reading in the time format and while that may work okay for the 24-hour clock, it’s going to cause an issue for the AM/PM. So let’s change the format using the comma style so it doesn’t read like a date. Then, the data looks as follows:

calculating time difference for am pm and 24-hour time

This might seem even more confusing until you realize what Excel is doing. It’s assigning a value between 0 and 1 for the time of day. The value of 0.33 indicates that the time is one-third of the day, which is what an eight-hour shift would be since 8/24 is the same as 1/3.

However, using 0.33 isn’t going to be too helpful if you need hours to be able to track how much someone should earn for that shift. To solve this, simply multiply the time difference by 24. Then, your time difference looks as follows:

calculating time difference for am pm and 24-hour time and converting into hours.

Now you see the eight hours that you may have first expected to see when calculating the difference between these times. Since it’s now reading as a number, you can multiply this by an hourly rate and arrive at the pay that is owing for the shift.

If you’re not looking for hours and just want the total minutes, then all you need to do is just multiply by a factor of 60 to convert the eight hours into 480 minutes.

Calculating hours worked when someone works the night shift

The above calculations work well if someone is working within the same day, but if someone starts at 9:00 PM and ends at 5:00 AM the next day, it’s not going to calculate properly if you simply take the difference between the numbers. In fact, the number would come out negative:

calculating time difference for am pm and 24-hour time and converting into hours.

That’s obviously not what you’ll want. To account for this, you’ll need to add an IF statement into our formula. The IF function should look at whether the end time is earlier than the start time. If it is, you’ll want to add 1 to the calculation. Here’s how the formula for the difference calculation would look like:

adjusting time calculations for overnight shifts.

I include the 24 in the calculation so that I don’t need to use an extra cell to convert the difference into hours. All the formula is doing is looking at if the end time is before the start time, and if it is, add a 1 to the end time before subtracting the start time from it. This method will work whether you’re using a 24-hour clock or the AM/PM format:

calculating time difference for am pm and 24-hour time and converting into hours.

This method will work even if someone works a 16-hour shift:

calculating time difference for am pm and 24-hour time and converting into hours.

Knowing how to calculate time difference in Excel isn’t difficult, and the key points to remember are as follows:

  1. Ensure the time is entered correctly, and
  2. Multiply the difference between the times by a factor of 24 for hours and another factor of 60 if you only want minutes.

Calculating time difference in Excel when the data is in text

If you need to know how to calculate time difference in Excel and you don’t have the luxury of the data being in the right format, the good news is it’s still possible to do so.

For instance, what if you want to enter the entire start and end times within one cell. In that case, you’re always going to be running into this situation. Suppose someone enters the following value into a cell: 9:00 AM-5:00 PM.

The format would be correct if there weren’t multiple times in that cell, which ensures it will read as text. That’s where you’ll need to use some data manipulation that involves using the MID function.

Let’s start by pulling the start time. For this, we’ll grab the numbers that come before “AM” but because it doesn’t matter whether it’s AM or PM, we’ll search for the letter “M” on its own:

=MID(A1,1,FIND(“M”,A1,1))

Here’s a breakdown of the formula:

  • Argument 1: A1 is the cell we’re looking at.
  • Argument 2: Start pulling from the first character.
  • Argument 3: The length of the text goes up until the letter “M” is found.

The formula will yield a result of 9:00 AM. However, you’ll still need to convert this into a number and you can do this by multiplying the result by 1 or putting it inside the TIMEVALUE formula:

=TIMEVALUE(MID(A1,1,FIND(“M”,A1,1)))

This will now give us a value of 0.375 and it can be used for the calculation. Next up, we’ll calculate the ending time. To do this, we’ll again use the MID function but the key is to start pulling the data after the dash (-) sign:

=MID(A1,FIND(“-“,A1,1)+1,LEN(A1))

This one is a bit more complicated, but let’s look at the different arguments again:

  • Argument 1: Still looking at cell A1.
  • Argument 2: Use the FIND function to get to the position where the dash (-) is at and then add a 1 onto that to ensure we’re starting from where the number begins and not the dash (-).
  • Argument 3: Even though you don’t need the total length of the string, you can use the LEN function to ensure the formula get everything that comes after the dash (-).

Doing this will give us a result of 5:00 PM and by again adding the TIMEVALUE function into it, it will give us a result of 0.7083

=TIMEVALUE(MID(A1,FIND(“-“,A1,1)+1,LEN(A1)))

You can now combine these formulas into one to do the calculation with text:

=TIMEVALUE(MID(A1,FIND(“-“,A1,1)+1,LEN(A1)))-TIMEVALUE(MID(A1,1,FIND(“M”,A1,1)))

This will result in 0.33 for the time difference, which is the correct result. However, with shifts that stretch into the following day, you’ll again run into the issue of how to deal with calculating time. If you don’t have night shifts to worry about, you can stop here.

But if you need to factor them in, then the easiest way to do so is using the AND and IF functions. We’ll want to look at whether the last two characters end in “AM” while also looking at whether “PM” is in the text:

=IF(AND(RIGHT(A1,2)=”AM”,(NOT(ISERROR(FIND(“PM”,A1,1))))),1,0)

Here’s a breakdown of the formula:

  • The first formula inside the AND function looks at the last two characters in the text to see if they equal “AM”
  • The second function looks for the characters “PM” anywhere in the cell. If it’s not found it will result in an error, that is why the formula begins with NOT and ISERRROR; you’ll want to add a 1 if it isn’t causing an error and “PM” is found.
  • If both of the above conditions are met, a value of 1 will be returned. Otherwise it will result in 0. This can now be added to the earlier time calculation formula, added to the end time.

Here’s how the complete formula will look like after adjusting the end time in case there’s a cross over from PM into AM:

=IF(AND(RIGHT(A1,2)=”AM”,(NOT(ISERROR(FIND(“PM”,A1,1))))),1,0)+TIMEVALUE(MID(A1,FIND(“-“,A1,1)+1,LEN(A1)))-TIMEVALUE(MID(A1,1,FIND(“M”,A1,1)))

It’s a messy formula but it is only adding the 1 to the end time if it’s a PM start time and ends in the AM. The only thing left would be to multiply this all by 24 to get the entire shift total in hours:

=24*(IF(AND(RIGHT(A1,2)=”AM”,(NOT(ISERROR(FIND(“PM”,A1,1))))),1,0)+TIMEVALUE(MID(A1,FIND(“-“,A1,1)+1,LEN(A1)))-TIMEVALUE(MID(A1,1,FIND(“M”,A1,1))))

The key thing to note is for this formula to work it’s important to leave a space between AM and PM. For example, if I were to just enter 9:00AM, Excel wouldn’t read that properly and it would result in an error. By leaving a space, it helps with parsing the data out, otherwise the formula would need to be even more complex. If it isn’t in that format, it’s preferable to first clean up the data as opposed to building a very complicated, nested formula using IFs and MIDs.

The above example goes over a few scenarios and obviously you can adjust the MID and other functions as needed based on how your inputs look. Pulling date calculations out from text is possible, it’s just no very pretty.


If you liked this post on how to calculate time difference in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.