date subtraction doesn't appear correctly vhen generating .log file-CodePudding

I currently have a log file that get generated when you click on a button.

At the beginning of the .log you have a preview with different information such as the login, url of a server etc... But you also have the date of the beginning of the process and the date for the end. Both are in the long date format and are displayed correctly in the log file.

That log file takes the information from a sheet the preview part is static and is a range ("A1:C13") and the rest of the log is beneath but still in the columns ("A:C").

The cells in excel :

I want to add a line that shows the difference between the two date, to quickly see the time the process took.

However when I'm creating the log file, I get the time difference in number. eg : in excel the cell with the format shows 00:01:55 but in the log file I get 0,001331

The output in the .log :

So far I tried :

To force the format in vba when the export in processing
To copy/paste in value with the hour format to not show the subtraction in the cell
different kind of format but it wasn't conclusive

You'll find the code that create the log file here :

Private Sub iExportLog(Optional LogPath As String, Optional bouton As String)
Dim NF As Integer, C As Range, posi As Integer
Dim date_nom As String
Dim drct As String
NF = FreeFile

drct = ThisWorkbook.path & "\_LogELB"
Set C = iFLog.Range("A1")

' iFLog = the sheet

' Things I tried :
'iFLog.Range("C12") = Format(iFLog.Range("C11").Value - iFLog.Range("C2").Value, "hh:mm:ss")

'iFLog.Range("C12").NumberFormat = "hh:mm:ss"
'

posi = InStrRev(ThisWorkbook.Name, ".")
If Dir(drct, vbDirectory) = "" Then MkDir (ThisWorkbook.path & "\" & "_LogElb")

date_nom = Format(CStr(Now), "yyyy_mm_dd_hh_mm_ss_")

If LogPath = "" Then LogPath = drct & "\" & bouton & "_" & date_nom & ".log"

Open LogPath For Append As #NF
Do While C.Value <> ""
    Print #NF, C.Value & vbTab & vbTab & vbTab & C.Offset(0, 1).Value & vbTab & C.Offset(0, 2).Value
    Set C = C.Offset(1, 0)
Loop
Close #NF
End Sub

I don't have a lot of practice with manipulating dates but I know that it can be painful.

Is there a way to display a date subtraction correctly when generating a .log/.txt file ?

CodePudding user response：

If you write a formatted time string to a cell then Excel will interpret that as a time value and "undo" your formatting, converting the value back to a numeric value (though the display format may hide that).

If you want to keep the "hh:mm:ss" format then first set the cell format to "Text", or prepend the string with ' before placing it in the cell. Or read the cell's Text property instead of Value