Set your start and end fields to time format (Format -> Number -> Time) and your time difference calculation field to duration format (Format -> Number -> Duration).
Once you have done that you can just add and subtract times like normal number to get the time difference. However, you will get a negative time if one event stretches past midnight....
The way I do it is:
place the date in row A1, in row A2 using the formula do = A1 + [number of Days]
(I format the date with an easy to interpret format (select row > Format > Number > More Formats > More date and time formats)
First thing to have in mind is that Google Sheets (as well as other spreadsheet apps) use serial numbers to handle dates, so when a value looks like 8/15/2019 (if it's handled as date and not as text) Google Sheets internally handle this value as 43692.
Recarding COUNTIF, this function requires two arguments and if you will use a comparison operator like &...
Walter. Let me suggest an entirely different (and shorter) approach:
or displayed differently:
A custom formula in the Conditional Formatting interface can do this for you. It looks like your dates in Column C are formatted as dates, and not just text. So I'll demonstrate a formula that will work in that case. (If your Column C is just text, you can adjust the formula to look for the strings "Saturday" or "Sunday".)
Select Cell D13. Click on the Fill ...
Your string is space-delimited, so we can cut it up using SPLIT, then grab the parts we need by number using INDEX.
The month needs a bit of coercing because it is a word, so I suggest asking the MONTH function for the date of that month's 1st, but then just the month-number of that month. It is able to convert the string "Mar1" to a date, for example, and ...
The short answer is yes, but I consider more convenient to use a custom function.
Google Sheets handle dates as serialized numbers. The timezone is based on the spreadsheet timezone settings. Unfortunately there isn't a built-in spreadsheet function that converts text as the one shown on the question directly, your formula should extract the parts and apply ...
Looking at you example spreadsheet the calculation in seconds is done with the following formula:
This is the breakdown of the formula:
time and dates in spreadsheets are floating point numbers.
D4-C4 is subtracting the start time from the end time.
The if statement is cheeking if the subtraction is negative, that ...