Explanation
In this example, the goal is to parse a text string into a proper Excel time .
First, note that the cells in F5:F13 are formatted as Text prior to entry . This allows the times to contain leading zeros like “083000”. Alternately, you can enter these time strings with a single quote at the start (’) to force Excel to respect them as text.
Next, the time string contains 6 characters in the following format:
hhmmss // as text
This means the formula needs to pick up 2 characters each for hour, minute, and second. Working from the inside out, this task is performed with the LEFT, MID, and RIGHT functions:
LEFT(E5,2) // get hh
MID(E5,3,2) // get mm
RIGHT(E5,2) // get ss
Each of the functions returns a result directly to the TIME function. In E9, we have:
041055
So the result inside TIME looks like this:
=TIME("04","10","55")
The TIME function then quietly handles the text-to-number conversion and returns a valid time:
04:10:55
Representing 4 hours, 10 minutes, and 55 seconds.
With a 4-character time string
The formula in C5 is meant to handle only a 4 character time string (hours and minutes), so the structure is a bit simpler. We simply hardcode the value for seconds into the TIME function as zero:
=TIME(LEFT(B5,2),MID(B5,3,2),0)
Explanation
In the worksheet shown, column B contains 12 dates. The goal is to calculate the previous working day before each date, taking into account weekends (Saturday and Sunday) and the holidays listed in column F. The formula should automatically skip weekends and any dates considered non-working days.
WORKDAY function
The WORKDAY function takes a date and returns the “next” working day n days in the future or the past . You can use the WORKDAY function to calculate things like project start dates, delivery dates, and finish dates that need to take into account working and non-working days. The generic syntax for WORKDAY looks like this:
=WORKDAY(start_date,days,[holidays])
Where days is a number (n) and holidays is an optional range that contains non-working dates. For this problem we want the previous working day, so we provide -1 for days . The formula in D5, copied down, looks like this:
=WORKDAY(B5,1,holidays)
Where holidays is the named range F5:F15, which contains dates that should be excluded . The WORKDAY function is fully automatic. Given a valid date, it will move forward or backward by n days, skipping weekends and holidays, until it lands on a working day. Notice we provide a negative 1 for days to move backward. Also, note that we have provided the holidays argument as the named range “holidays” (F5:F15). Without a named range, use an absolute references like $F$5:$F$15:
=WORKDAY(B5,-1,$F$5:$F$15)
As the formula is copied down, it returns the previous business day before the starting date in column B. Saturdays and Sundays are automatically skipped, as well as dates that appear in the range F5:F15.
Current or previous workday
A common scenario in business is that you have a list of dates that may or may not be working days, and you want to shift dates that are not working days back to the previous working day and leave the other dates alone. In that case, you can adjust the formula as follows:
=WORKDAY(B5+1,-1,holidays)
In this version of the formula, we add 1 day to the date inside the WORKDAY function. WORKDAY then moves back one day to the original date and checks the result. If the original date is a working day, WORKDAY returns it. Otherwise, WORKDAY will continue moving back one day at a time until it finds a valid workday, skipping weekends and holidays along the way. You can see the result of this alternate formula in the screen above. For a practical example of this approach, see this formula for semimonthly pay dates .
Custom weekends
The WORKDAY function defines a weekend as Saturday and Sunday only. If you need to provide a more custom workday schedule, switch to the WORKDAY.INTL function instead. For example, to calculate the previous working day in a 4-day workweek where weekends are Friday, Saturday, and Sunday, you can use WORKDAY.INTL like this:
=WORKDAY.INTL(B5,-1,"0000111",holidays)
WORKDAY.INTL includes an optional argument called weekend that can be provided as a string of 1s and 0s like “0000111”. In this scheme, the week begins on Monday and there are 7 characters one for each day of the week. A 1 indicates a weekend and 0 indicates a workday. For more details, see How to use the WORKDAY.INTL function .
