As show in this image:  I have those 2 cells with DATETIME and I need to do the delta of those in order to get Day - HH:mm:ss. How can I do that? If i try to use  I get error. Any tip? What i wanna get:
93606
I've got a Google Spreadsheet that looks roughly like this:   Date        | Start time  | End time    | Minutes  ------------+-------------+-------------+-----------  1/11/2012   | 11:39       | 12:41       | ?!  ------------+-------------+-------------+-----------              |             |             |    Right now if I fill in the number of minutes between the two times of day by hand. Is there a straight-forward way to calculate a time delta and have the spreadsheet do it for me?
61077
I'm currently using  \usepackage[UKenglish]{babel} \usepackage{datetime}   and I'm getting  Sunday 4th September, 2016  with the commands \today or with \newdate{example}{01}{09}{2016} (outside of \begin{document}) and \displaydate{example} inside the document.  How can I reformat this date to not have the name of the day in it (Best would be with using datetime2)? So like 4th September, 2016. With the "th" as a high letter like this:   Full document example:  \documentclass[a4paper, 12pt, DIV=calc, headings=small]{scrreprt} %Used packages \usepackage[UKenglish]{babel} \usepackage[utf8]{luainputenc} \usepackage{datetime}  %Other definitions \pagenumbering{arabic}  \newdate{sample}{31}{01}{2017}   \begin{document} \displaydate{sample}\\ \today \end{document}
102384
I am still not able to get the correct difference, really struggling to make it working. The business hour set from 10 AM to 7 PM  BusinessHours bh = [SELECT FridayEndTime,FridayStartTime,Id,MondayEndTime,MondayStartTime,                     FROM BusinessHours where Name='Test Business Hour'                     LIMIT 1]; Time startTime = bh.MondayStartTime;        Time endTime = bh.MondayEndTime;  Date d = Date.today(); Datetime monTodaystart = Datetime.newInstance(d.year(),d.month(),d.day(),startTime.hour(), startTime.minute(),startTime.second()); Datetime monTodayend = Datetime.newInstance(d.year(),d.month(),d.day(),endTime.hour(), endTime.minute(),endTime.second()); System.debug('monTodaystart :: '+monTodaystart);     System.debug('monTodayend :: '+monTodayend);   Long diff = BusinessHours.diff(bh.Id, monTodaystart, monTodayend); System.debug('diff  :: '+diff);   I tried this simple example, but still its not working why ? How we can manipulate it ?  Datetime monTodaystart = Datetime.newInstance(2017,8,20,10,0,0); Datetime monTodayEnd = Datetime.newInstance(2017,8,20,17,0,0); System.debug('ID : '+bh.Id);  Long diff = BusinessHours.diff(bh.Id, monTodaystart,monTodayEnd); System.debug('diff  :: '+diff);   I dont think I should used the API here, instead I should use my custom logic, looks like some bug in the API still exists.
83311
I want to navigate through all the files in a folder and find out the missing file for a specific date.  Files are partitioned by hour and file name have yyyy-mm-dd-hh formatting.   So between 2017-07-01 and 2017-07-02 there will be 24 files from 2017-07-01-00 through 2017-07-01-23  How can I find missing hourly file if I pass above dates as start and end date?   Appreciate any input!
4851
I am trying to format cells in Excel 2013. I want the cells to get colored as green, yellow, and red based on how close they are with the target date (January 13, 2015 in this case).  I am unable to use conditional formatting as Excel says that relative formatting is not permitted within conditional formatting.   Target:   06-01-2015  01-01-2015 02-01-2015 03-01-2015 04-01-2015 05-01-2015 06-01-2015 07-01-2015 08-01-2015 09-01-2015 10-01-2015   If the date in the cell is more than 2 days of the target date, I want the cell to change color to Green. I used =DATEDIF($N12,$N$9,"d")&gt;=3 and the format changed to green.  If the date in the cell is exactly 2 days near the target date, I want the cell to change color to yellow. I used =DATEDIF($N12,$N$9,"d")=2 and the format changed to yellow.  If the date in the cell is within 2 days of the target date, I want the cell to change color to red. I used =DATEDIF($N12,$N$9,"d")&lt;2 and the format changed to red only for 5th and 6th January. Apparently, it does not recognize negative values.  How can I get this to match the required colors?
25978
Presently the date is showing as mmm dd hh:mm:ss. Is there any way that I can change it to as per my liking. For example : dd mmm, hh:mm:ss.
76797
I'm using a DateTime in C# to display times. What date portion does everyone use when constructing a time?  E.g. the following is not valid because there is no zero-th month or zero-th day:  // 4:37:58 PM DateTime time = new DateTime(0, 0, 0, 16, 47, 58);   Do I use COM's zero date?  // 4:37:58 PM DateTime time = new DateTime(1899, 12, 30, 16, 47, 58);   Or perhaps SQL Server's?  //4:37:58 PM DateTime time = new DateTime(1900, 1, 1, 16, 47, 58);   I realize it's arbitrary, since I'll be ignoring the date portions in code, but it would still be nice to be able to use:  DateTime duration = time2 - time1;     Answer  I think I like MinValue   DateTime time = DateTime.MinValue.Date.Add(new TimeSpan(16, 47, 58));     Note: I can't use a TimeSpan, because that doesn't store times of the day. And the reason I know that is because there's no way to display its contents as a time.  Which is to say that TimeSpan records a span of time, not a time of day, e.g.:  TimeSpan t = new TimeSpan(16, 47, 58); t.ToString();   returns a span of time in the format hours:minutes:seconds, e.g.:  16:47:58   rather than a time:  4:47:58 PM    (United States) 04:47:58 nm   (South Africa) 4:47:58.MD    (Albania) 16:47:58      (Algeria) 04:47:58 م    (Bahrain) PM 4:47:58    (Singapore) 下午 04:47:58  (Taiwan) 04:47:58 PM   (Belize) 4:47:58 p.m.  (New Zealand) 4:47:58 μμ    (Greece) 16.47.58      (Italy) 오후 4:47:58   (Korea) 04:47:58 ب.ظ  (Iran) ਸ਼ਾਮ 04:47:58   (India) 04:47:58 p.m. (Argentina) etc   In other words, there is a difference between a timespan, and a time. And also realize that TimeSpan doesn't provide a mechanism to convert a span of time into a time of day - and there is a reason for that.
36098
I would like my target completion column to have a date that is greater than my start column. Excel supports a date validation option but I do not see this in Google Sheets.  Is it there and I just don't know how to use it?  I saw this question:  but I can't see how to get it to apply to the same row in the other column.  Obviously I don't want a new rule on each cell.
44039
I am looking to calculate time difference between the two below  Value1=2016-10-13 14:19:23 Value2=2016-10-13 18:19:23    I want to get difference in the form of hours/minutes. Any quick solution available?.
22444
Need help in adding another function using the below script. I would like to have a timestamp "start" and "end" on two different columns, whenever the other two separate columns are edited. Based on the below script, I could only have one time stamp, from one column which is the column 2 and time stamp on column 10.  function onEdit()  {  var s = SpreadsheetApp.getActiveSheet();  if( s.getName() == "Sheet1" ) { //checks that we're on the correct sheet    var x = s.getActiveCell();    if( x.getColumn() == 2 ) { //checks the column      var nextCell = x.offset(0, +8);      if( nextCell.getValue() === '' ) //is empty?        var time = new Date();      time = Utilities.formatDate(time, "GMT", "hh:mm");        nextCell.setValue(time);    };  }; }
19945
I have a table that has just two columns, Date and Profit:  Date            Profit 24 - March      25.00 24 - March      19.85 25 - March      5.25 26 - March      6.36 26 - March      10.18 26 - March      35.63   I want to calculate and display the average daily profit in one cell.  Please keep in mind that when I enter data for new dates such as 27 - March and it's profit, it should update the average daily profit cell.  Some days I do not make any sale; for example, if I make sales and profit for march 24, march 25 and march 27, I did not make a sale on March 26, I want it to calculate it regardless of me making a sale on a date or not. Meaning in a month that has 31 days, it should divide by 31 regardless on whether I made a sale on a specific date or not. Would that be possible?
18763
I want a formula that will return a date that depends on the date in another cell, but modified by the day of the week of the date in that cell.   If cell A1 has a date value that is a Sunday, Monday, or Tuesday, I want cell A2 to return the date of the Friday before (e.g. if A1 is Monday 16 Oct 2017, A2 would return Friday 13 Oct 2017). If the date in cell A1 is Wednesday through Saturday then the date in cell A2 would be two days prior to the day (e.g. If A1 is Thursday 19 Oct 2017 then cell A2 would return a date of Tuesday 17 Oct 2017).   I want the formula to do all of the above calculations in one formula.
108546
How do I Change cell color in Excel 2010 for upcoming or past dates based on 90 days from today. I would be great if I could use 2 color scale but one color will be fine.
117617
I need to plot the decay of my phone's battery against time over night. My starting time is 23:17:47 and my ending one is 07:05:49. I don't have any issue in changing the time format in the current one, selecting an XY chart and plotting time on the X axis.   The problem arises because Excel doesn't start the X axis at 23:17:47, but instead at 00:00:00, so my first value is drawn from the end of the graph and the goes back at the beginning when time values are over 00:00:00.   In other words, how do I set up the min time value to 23:17:47 and the max to 07:05:49 in excel?
62923
I have two dates as the following , I want to get the duration spent between them.   6/29/2015 12:18:04 PM    Regards,
16732
I'd like to have a time, say 6:45am, and add an amount of hours, say 1.45 hours, to result in another time. So I'd like to add 1.45 hours to 6:45am to get another time.   Is there a command line utile for that? I've done some Googling, and read the man page for date and haven't found anything like that. wcalc doesn't seem to handle time calculations.  EDIT: Mar 6, 2015. This is the script I ended up with to use decimal hours. It could use some error checking to make sure HH:MM uses 2 digits for the hours.  #!/bin/bash # Mar 6, 2015 # Add decimal hours to given time.  # Syntax: timeadd HH:MM HOURS # There MUST be 2 digits for the hours in HH:MM. # Times must be in military time.  # Ex: timeadd 05:51 4.51 # Ex: timeadd 14:12 2.05 echo " " # If we have less than 2 parameters, show instructions and exit. if [ $# -lt 2 ] then     echo "Usage: timeadd HH:MM DECHOURS"     exit 1 fi intime=$1 inhours=$2 # Below is arithmetic expansion $(()) # The bc calculator is standard on Ubuntu.  # Below rounds to the minute.  inminutes=$(echo "scale=0; ((($inhours * 60)*10)+5)/10" | bc) echo "inminutes=$inminutes" now=$(date -d "$intime today + $inminutes minutes" +'%H:%M') echo "New time is $now"
100784
I can't seem to grab the month, day and year using   awk '{print $2, $3, $6}' date    Does anyone know how to?