How to compare dates or date against today with query on google sheets?

There’s no today() in Query. Use now() instead: =query(sheet1!A3:N, ” select I,M where I = ‘Singapore’ AND M > now() “,0) Or if you want now() without time(equivalent to TODAY()), use: todate(now()) For this to work, provided you have all the correct dates in M in any format, which Google sheets recognises (i.e., the formula … Read more

Difference between two dates expressed as years, months, days (in one column)

=IF(DATEDIF(A1, B1, “D”)>365, QUOTIENT(DATEDIF(A1, B1, “D”), 365)&” year(s) “& QUOTIENT(MOD(DATEDIF(A1, B1, “D”), 365), 30)&” month(s) “& MOD(QUOTIENT(MOD(DATEDIF(A1, B1, “D”), 365), 30), 30)&” day(s)”, IF(DATEDIF(A1, B1, “D”)>30, QUOTIENT(DATEDIF(A1, B1, “D”), 30)&” month(s) “& MOD(DATEDIF(A1, B1, “D”), 30)&” day(s)”, DATEDIF(A1, B1, “D”)&” day(s)”))

Google Apps Script date format issue (Utilities.formatDate)

The Utilities.formatDate() utility formats a javascript String. However, when written out to the spreadsheet, the new row of data is interpreted by Google Sheets to determine data types and appropriate formatting, just as if you’d typed it in through the user interface. Since you’ve got a recognizable date string, Google Sheets decides it’s a Date, … Read more

VBScript How can I Format Date?

0 = vbGeneralDate – Default. Returns date: mm/dd/yy and time if specified: hh:mm:ss PM/AM. 1 = vbLongDate – Returns date: weekday, monthname, year 2 = vbShortDate – Returns date: mm/dd/yy 3 = vbLongTime – Returns time: hh:mm:ss PM/AM 4 = vbShortTime – Return time: hh:mm d=CDate(“2010-02-16 13:45”) document.write(FormatDateTime(d) & “<br />”) document.write(FormatDateTime(d,1) & “<br />”) … Read more

How to convert ISO Date to UTC date in Hive

Hive understands this format: ‘yyyy-MM-dd HH:mm:ss.SSS’. Use unix_timestamp() to convert to seconds passed from 1970-01-01, then use from_unixtime() to convert to proper format: select from_unixtime(UNIX_TIMESTAMP(“2017-01-01T05:01:10Z”, “yyyy-MM-dd’T’HH:mm:ss’Z'”),”yyyy-MM-dd HH:mm:ss”); Result: 2017-01-01 05:01:10 Update. This method is to remove Z and replace T with space using regexp_replace and convert to timestamp if necessary, without using unix_timestamp(), this will … Read more

Date does not display from Model on HTML input type date

If your using this to generate the browsers HTML5 datepicker implementation, the format of the date needs to be yyyy-MM-dd (ISO format). Using TextBoxFor() it needs to be @Html.TextBoxFor(m => m.InitialDate, “{0:yyyy-MM-dd}”, new { @class = “form-control”, type = “date” }) Alternatively, add the following attributes to the property [DataType(DataType.Date)] [DisplayFormat(DataFormatString = “{0:yyyy-MM-dd}”, ApplyFormatInEditMode = … Read more