> [!tldr] A couple of handy formulas Because [[Dates in Excel]] don't respect [[ISO 8601]]... here's some tips for working with [[Spreadsheet]]s and dates. # Dealing with [[Universal Coordinated Time|time zones]] & [[ISO 8601#UTC Timestamp]]s If the timestamp is a [[Universal Coordinated Time|UTC]] timestamp (i.e. one that ends with "Z")... you should probably just give up. Honestly. If you **HAVE** to make it work, that means you're **hardcoding the timezone offset** in addition to the usual scenanigans (use [[#Formulas]] below, then add or subtract your offset). # Formulas >[!note] These formulas use `a1` as the cell holding the [[ISO 8601]] timestamp ## Getting the **date & time** Thank the [[ISO]] for enforcing a common number of characters in every timestamp. `=datevalue(left(A1,10))+timevalue(mid(A1,12,8))` ## Just getting the **date** only `=datevalue(left(A1,10))` ## Just getting the **time** only `=timevalue(mid(A1,12,8))` ### Averaging Over Midnight - **Use case**: average bedtime. - **Problem**: 11:59PM and 12:01AM is like averaging 0.99 with 0.01 → average ends up being **noon**, which isn't right - **Solution**: Add `1` to the 12:01AM. - Formula that can help: `if(timevalue(mid(A1,12,8))<0.5,mid(A1,12,8)+1,mid(A1,12,8))` **** # More ## Source - self