2

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....


2

The way I do it is: place the date in row A1, in row A2 using the formula do = A1 + [number of Days] Eg.: A1 [11/12/2018] A2 [=A1+3] A2 [November/15/2018] (I format the date with an easy to interpret format (select row > Format > Number > More Formats > More date and time formats)


1

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 &...


1

Walter. Let me suggest an entirely different (and shorter) approach: =IF((TIMEVALUE(H2)*24*60)>5,0,CHOOSE(CEILING(((TIMEVALUE(H2)-TIMEVALUE("00:00:30"))*24*60),0.5)/0.5,150,100,75,50,40,35,30,25,25,0)) or displayed differently: =IF( (TIMEVALUE(H2)*24*60)>5,0, CHOOSE( CEILING(((TIMEVALUE(H2)-TIMEVALUE("00:00:30"))*24*60),0.5)/0.5, 150,100,75,50,40,35,...


1

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 ...


1

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 ...


1

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 ...


1

Looking at you example spreadsheet the calculation in seconds is done with the following formula: =86400*(if(D4-C4<0,1,0)+D4-C4) 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 ...


Only top voted, non community-wiki answers of a minimum length are eligible