Range contains specific text

Get next day of week

=date+7-WEEKDAY(date+7-dow)

If you want to find the next specific day of week (i.e. the next Wednesday, or Friday, or Monday) given a specific start date, you can use a generic formula that uses the WEEKDAY function with something called "day of week" (dow). With this formula, you can do things like:

• 
  Get the next Friday
  
• 
  Get the next Saturday
  
• 
  Get the next Tuesday
  

How the formula works

Formulas that use the concept of "day of week" are based on the idea that each day of of the week has a specific number. By default, Excel assigns 1 to Sunday and 7 to Saturday. So, the WEEKDAY function, when given a date, will return a number between 1 and 7. In a nutshell, this formula first rolls the date forward by 7 days, then rolls back to the correct date by subtracting the result of a calculation that uses the WEEKDAY function.

In the example formula, D6 is the date 1/16/2015, and the formula in C6 is:

=B6+7-WEEKDAY(B6+7-2)

The number 2 represents the day of week (dow) that you want, in this case, Monday.

To solve this formula, Excel first adds 7 to the starting date in C6, then reduces B6+7-2 to 42025 (the serial number in Excel's date system that represents 1/21/2015). Next, Excel calculates the day of week for that date (4) and subtracts that from the the original start date + 7 (42027 = 1/23/2015) to get a final date value of 42013 = 1/9/2015

=B6+7-WEEKDAY(B6+7-2)

=42027-WEEKDAY(42025)

=42027-4

=42023

=1/9/2015

If you want to get the next day of week from the current date, you can use the TODAY function, like so:

=TODAY()+7-WEEKDAY(TODAY()+7-dow)

Note: If the date supplied is the same day of week you're want to retrieve, the same date will be returned.

Get most recent day of week

=date-MOD(date-dow,7)

If you want to find the most recent Wednesday, or Saturday, or Monday, given a specific start date, you can use a formula that uses the MOD function.

How the formula works

Formulas that use the concept of "day of week" are based on the idea that each day of of the week has a specific number. By default, Excel assigns 1 to Sunday and 7 to Saturday.

In the example formula, B6 is the date 1/16/2015, and the formula in C6 is:

=B6-MOD(B6-7,7)
The number 7 (the number argument in the MOD function) represents the day of week (dow) that you want.

To solve this formula, Excel first subtracts the dow (7 in this case) from the date, then feeds the result into the MOD function as the number. MOD returns the remainder of dividing that number by 7, which is then subtracted from date.

=B6-MOD(B6-7,7)

=B6-MOD(42013,7)

=B6-6

=42014

=1/10/2015
If you want to get the most recent day of week from the current date, you can use the TODAY function, like so:

=TODAY()-MOD(TODAY()-dow,7)

Note: If the the date has the same day of week, date will be returned.

Get month name from date

=TEXT(date,"mmmm")

If you need to get the month name (i.e. January, February, March, etc.) from a date, you have several options depending on your needs.

Do you just want to display the month name?

If you only want to display a month name, you don't need a formula – you can use a custom number format. Select the date and navigate to Format cells (Ctrl + 1 or Cmd +1), then select Custom and enter one of these custom formats:

"mmm" // "Jan"

"mmmm" // "January"

Excel will display only the month name, but it will leave the date value intact.

Do you want to convert the date into a month name?

If you want to convert the date value to a text value, you can use the TEXT function with a custom number format like "mmm". In the example, the formula looks like this:

=TEXT(B4,"mmmm")

The TEXT function converts values to text using the number format that you provide. Note that date is lost in the conversion: only the text for the month name remains as text.

A more flexible way

If you want maximum flexibility, you can "map" the month to a month name directly in the CHOOSE function like so:

=CHOOSE(MONTH(B4),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

Enter the month names you want to return (abbreviated or not) as values in CHOOSE, after the first argument, which is entered as MONTH(date). MONTH will extract a month number, and CHOOSE will use this number to return the nth value in the list. This works because MONTH returns a number 1-12 that corresponds to the month name.

CHOOSE is more work to set up, but it is also more flexible, since it allows you to map a date toany values you want (i.e. you can use values that are custom, abbreviated, not abbreviated, in a different language, etc.)

Calculate elapsed work time

=end-start

If you need to calculate elapsed time you can use a simple formula that simply subtracts the start time from the end time. However, when times cross a day boundary, things can get tricky. Read below to see several ways you can calculate elapsed time, depending on the situation.

The basics

In Excel, one day (24 hours) is represented by the number 1. So 1 hour is 0.041666667 (i.e. 1/24), 8 hours is 0.333, 12 hours is 0.50 and so on. In short, you can think of hours as fractional pieces of a day.

When the start time and end times are both in the same day, then the start time is, by definition, less than the end time and you can use simple subtraction to figure out elapsed time. For example, with start time of 9:00 AM and an end time of 5:00 PM, you can simply use this formula:

end - start = elapsed time

0.375 - 0.708 = .333 // 8 hours

Formatting elapsed time

To format elapsed hours it can be useful to use a custom format like h:mm or [h]:mm

The special square bracket syntax [h] tells Excel to allow hour durations of greater than 24 hours. If you don't use the brackets, Excel will simply "roll over" when the duration hits 24 hours (like a clock).

The problem

Calculating elapsed time is more tricky if the times cross a day boundary. For example, if thestart time is 10:00 PM one day, and the end time is 5:00 AM the next day, the end time is actually less than the start time and the formula above will return a negative value which will cause Excel to display a string of hash characters (i.e. ########).

To correct this problem, you can use this formula for times that cross a day boundary:

=1-start+end

By subtracting the start time from 1, you get the amount of time in the first day, which you can simply add to the amount of time in the 2nd day, which is the same as the end time.

This formula won't work for times in the same day, so we can generalize and combine both formulas inside an IF statement like so:

=IF(end>start, end-start, 1-start+end)

Now when both times are in the same day, end is greater than start time, so the simple formula is used. But when the times across a day boundary the second formula is used.

This can be further simplified to this elegant formula:

=MOD(end-start,1)

Here MOD function takes care of the negative problem by using the MOD function to "flip" negative values to the required positive value.

A thorough discussion of modulo is beyond the scope of this article, but here's a good link on Khan Academy.

So, the formulas above will handle either case (both times in the same day, or start in one day and end in the next). However, note that they only work for times that span just one day. If times span more than one day, you'll need a different approach. One approach is to use both date and time, as explained below.

Simply the problem - use date and time

If you don't like the complexity of the above solutions, or if you need to calculate elapsed time that spans more than one day, an easy fix is to simply add a date value to both the start and end times. For example, you can enter September 1, 2016 at 9:00 AM as follows, with a single space between time and date:

9/1/2016 10:00 AM

Because this stores both the date and time, you can always subtract the start from the end and get a correct result.

For example, to calculate the elapsed hours between September 1, 2016 at 9:00 AM and September 3 at 10:00 AM, enter both values as dates plus times, then subtract the start from the end and use [h]:mm to format the result.

Note that when you use a date and time, you can format the values any way you like. You can apply a format that shows the date with a time, or simply format to show time only.

The formula in D8 the screen shot example at the top of the page uses this approach. The formula is simply:

=C8-B8

The result is 2.042, which, when formatted using [h]:mm, is 49:00 hours.