Explanation
The COUNTIFS function takes one or more criteria, entered as range/criteria pairs. In this example, the first range/criteria pair is:
B5:B11,">="&E5
Matching any time greater than or equal to the time E5 (5:00). The second range/criteria pair is:
B5:B11,"<"&E6
Matching any time less than the time in E6 (6:30).
With hard-coded values
The formula in E7 could be written with hard-coded time values as follows:
=COUNTIFS(B5:B11,">=5:00",B5:B11,"<6:30")
Excel translates a string like “5:00” into the correct numeric time value.
With the TIME function
The formula in E7 could be written with the TIME function like this:
=COUNTIFS(B5:B11,">="&TIME(5,0,0),B5:B11,"<"&TIME(6,30,0))
The TIME function provides a simple way to assemble a valid time using discreet hour, minute, and second values.
Explanation
The TEXT function takes numeric values and converts them to text values using the format you specify. In this example, we are using the format “mmm d” for both TEXT functions in E5. The results are joined with a hyphen using simple concatenation.
Note: the other examples in column E all use different text formats.
End date missing
If the end date is missing, the formula won’t work correctly because the hyphen will still be appended to the start date (e.g.“March 1 - “).
To handle this case, you can wrap the concatenation and second TEXT function inside IF like so:
=TEXT(date1,"mmm d")&IF(date2<>""," - "&TEXT(date2,"mmm d"),"")
This creates the full date range when both dates are present, but outputs only the start date when the end date is missing.
Start date missing
To handle a case where both dates are missing, you could nest another IF like this:
=IF(date1<>"",TEXT(date1,"mmmm d")&IF(date2<>""," - "&TEXT(date2,"mmm d"),""),"")
This formula simply returns an empty string (””) when date1 is not available.
