calculate difference in time

Here is a power Query solution

Please Login or Register  to view this content.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

One way and custom format cells [mm]:

Formula:

Please Login or Register  to view this content.

Will move this to the Google sub forum so there will be no confusion

Did you see post #4?

It appears to me you have the same problem so many others have with dates in spreadsheets. Your dates are not numbers, they are text strings [=is text(B2) returns true]. This leaves you at the whims and mercy of the spreadsheet you're using how it will interpret these text strings, if it even chooses to interpret the text as a number. As an example of these whims, my copy of Sheets has no trouble with Flyboy's simple formula. I suspect that is because my regional settings are such that Sheets is able to recognize these as NDY dates and convert correctly to number, but you settings don't allow Sheets to see that these are dates.

You haven't said anything about your data entry/import process. IMO, the best thing you could do is figure out how to enter/import the data so it will be permanently stored as a number. That might mean changing regional settings or changing to DMY dates in the source text file or something else. If you will enter/import your dates as numbers and not text, you will have a much easier time working with those values.

the result is #VALUE!

Sorry, the cell reference was not correct, please try this formula ande copy down:

Formula:

Please Login or Register  to view this content.

Is there a way I can change the settings of the sheet to recognise the entries as NDY dates?

I don't have much experience with Sheets, but this essay mentions that, "To ensure correct calculations, it's vital to have the correct locale set." [emphasis mine] https://www.ablebits.com/office-addi...e-date-format/ If you are going to store "dates as text" and receive those dates in an MDY type of format, it will be important to change the spreadsheet's locale settings to something that will readily accept MDY dates.

The other choice might be, since you know where the data are coming from, is to go into Integrately and have Integrately output the log file time stamps in a format more compatible with your default locale settings. Again, while I have limited experience with Sheets, what experience I have suggests that Sheets may automatically import and store dates as numbers rather than text if the date strings in the text file match Sheets' locale settings.