Take the value without automatically converting it-CodePudding

So I have a cell that I need to read which has the value 375:00:00. When looking at the formula I can see that the value is 14.01.1900 15:00:00. When trying to read the value of the cell I only get the Date and not the 375.

My approach looks like this but I am not sure on how to proceed with converting the cell value?

Sub Workbook_Open()
        Dim TimeWB As Workbook
        Set TimeWB = Workbooks.Open(Filename:="J:\XXX\XXX\XXX\2022\XXX.xlsx", Password:="XXX")
        
        Dim TimeUS As String
        TimeUS = TimeWB.Sheets("Übersicht").Range("E3").Value
    
        'Convert? The given Value (15,625) to back to 375:00:00 ?
    
        'MsgBox Displays 15,625
        MsgBox (TimeUS)
End Sub

Is there some kind of built-in function to deal with this? CDate wasn't quite working here.. but I guess this isn't even supposed to be working here.

CodePudding user response：

Either use

TimeUS = TimeWB.Sheets("Übersicht").Range("E3").Text

to get it in the same format as the number format of the cell.

Or use the numeric .Value and format it to whatever you like:

TimeUS = Format$(TimeWB.Sheets("Übersicht").Range("E3").Value, "[h]:mm:ss")

// Edit

As Rory pointed out [h] does only work in .NumberFormat but not in Format(). Therefore you can use a workaround:

Public Function FormatTimeInHoursOnly(ByVal TimeVal As Date) As String
    FormatTimeInHoursOnly = CLng(Int(TimeVal * 24)) & Right$(Format$(TimeVal, "hh:mm:ss"), 6)
End Function

And use it like

    TimeUS = FormatTimeInHoursOnly(TimeWB.Sheets("Übersicht").Range("E3").Value)

CodePudding user response：

Quite an intriguing case here. Evidently, 15.625 is produced by Excel interpreting this as [h]:mm:ss. 375 / 24 = 15.625. So, to get back to 375:00:00, you could just do 15.625 * 24 & ":00:00". When you assign this to a cell, Excel will interpret it as a value, rather than a string (but see below...). However, there is something very odd here. You said:

When looking at the formula I can see that the value is 14.01.1900 15:00:00.

This is odd. Obviously, it should resolve to 15.01.1900 etc. I thought you might have made a typo, but apparently not. I've ran some tests:

Sub test()

For Each cell In Range("A1:A4")

'Range("A1") = 375:00:00 custom formatting automatically applied: "[h]:mm:ss"
'Range("A2") = 375:00:00 custom formatting manually adjusted: "mm/dd/yyyy h:mm:ss AM/PM"
'Range("A3") = "=VALUE(""375:00:00"")", defaults to general formatting: 15.625
'Range("A4") = "=VALUE(""10000:00:00"")" from 10000 this produces #VALUE! errors (or "Error 2015")

Debug.Print cell.Value
Debug.Print cell.Value2
Debug.Print cell.Text
Debug.Print "---"

Next cell

'''Immediate Window output

'     15.625
'     15.625
'    375:00:00
'    ---
'    1/14/1900 3:00:00 PM
'     15.625
'    01/15/1900 3:00:00 PM
'    ---
'     15.625
'     15.625
'    15.625
'    ---
'    Error 2015
'    Error 2015
'    #VALUE!
'    ---

'''

End Sub

So, two very odd things here:

cell.Value for Range("A2") is given as 1/14/1900 3:00:00 PM (reproducing the quirk in the question mentioned about), even though .Text will correctly produce 01/15/1900 3:00:00 PM.
Excel ceases to understand the "logic" at 10000:00:00. Compare this:

Sub insert_convert()

Range("A6") = 10000 / 24 * 24 & ":00:00"    'expected 416.6666667, but now a String: "10000:00:00"
Range("A7") = 9999 / 24 * 24 & ":00:00"     '416.625 (and "2/19/1901  3:00:00 PM" in formula bar)

End Sub

I fail to see why these oddities occur. Seem like bugs to me.

Edit: I guess problem 2 might have to do with the fact that during the evaluation of =VALUE("10000:00:00"), Excel tries to interpret the string as a Date, and then errs, since:

[Date h]olds IEEE 64-bit (8-byte) values that represent dates ranging from January 1 of the year 0001 through December 31 of the year 9999...