Adding Hours to a Date

[PocketNeo]

I need to subtract hours from a date/time value using a formula. The results I have tried dont seem to work.

What i've tried:
Using a formula in B2 like =A2-4/24 to reduce 4 hours doesn't work. It returns #VALUE !
I have also tried =DATEVALUE(A2) and it returns #VALUE !
I have also tried to change the date format to DATE and also to TIME, and to the custom format (see below), to no avail!

What I want:
I have a 2 column spreadsheet:
In Col A, I have a date with GENERAL format type (when i open the workbook). The date itself is formatted as (mm/d/yyyy hh:mm:ss AM/PM). See example below.
In Col B, its blank, with GENERAL format type. This is where I want to place the formula.

COL A COL B
UTC_Date EDT_DATE
10/4/2019 9:35:49 AM


Thank you!

 

[Joe4]

In Col A, I have a date with GENERAL format type (when i open the workbook). The date itself is formatted as (mm/d/yyyy hh:mm:ss AM/PM).

If column A is in GENERAL format, and it shows the entry as "10/4/2019 9:35:49 AM", then you have a Text entry, not a Date entry.
You can quickly convert those Text entries to valid Date/Time entries using "Text to Columns", and choose the Date option, with MDY to convert.

Even if A2 was a Text entry, the formula in B2 of:
=A2-4/24
should work, which leads me to believe you may have some other extra hidden characters in cell A2.
If the Text to Columns does NOT successully convert it over, then we need to look a bit more closely exactly what is in cell A2.

 

[PocketNeo]

Thank you for the reply.
I tried to strip any hidden characters by copying my column A of dates into Notepad, then pasted it back into a blank sheet (column A).
In b2, I did the same formula =A2-4/24 and got the #VALUE ! error.

What am I doing wrong or what can i do?

 

[Joe4]

Please do the following for us:
1. Please change the format of cell A2 to General (if you have not already done so)
2. Please copy and paste the EXACT entry shown in cell A2
3. Place the formula in any blank cell, and let us know what it returns:
=LEN(A2)

Also, are you using an American or European version of Excel?

 

[PocketNeo]

Copied 10/4/2019 9:35:49 AM into a fresh sheet, GENERAL format, in cell A1.
B2 =LEN(A1) returns 20.
C2 = DATEVALUE(A1) returns #VALUE !

I am using Microsoft office 365 ProPlus. I am in America so use that version.

 

[Joe4]

Place this User Defined Function I created in a VBA module in your worksheet.

Function ReturnASCII(myEntry As String) As String

    Dim ln As Long, i As Long
    Dim temp As String

    ln = Len(myEntry)
    
    If ln > 0 Then
        For i = 1 To ln
            temp = temp & Asc(Mid(myEntry, i, 1)) & "-"
        Next i
        ReturnASCII = Left(temp, Len(temp) - 1)
    End If

End Function

Then, enter this formula in any blank cell, and tell me what it returns:
=ReturnASCII(A2)

 

[PocketNeo]

I put the code on the sheet, and the formula with value of the date in question in cell A1.
I changed the formula to =ReturnASCII(A1), and it returned "# NAME ?"(the date used was 10/4/2019 9:35:49 AM)
I also tried the same in the sheet where I had the original problem, without changing anything other then the cell reference where the date was, and it returned the same result.

 

[Joe4]

That means it is not seeing the function, which means that you have probably placed it in the wrong place/module.

Here is an easy way to make sure that you put it in the correct place (a new module in that Workbook).
1. Make sure that you are in the workbook you want to apply this to
2. Turn on your Macro Recorder (View -> Macros -> Record Macro)
3. Note the Macro name (something like "Macro1") and click OK
4. Stop the Macro Recorder (View -> Macros -> Stop Recording)
5. Bring up all your Macros (View -> Macros -> View Macros)
6. Highlight the macro you just recorded, and click "Step Into"
7. Click the "Stop" button from the VB Editor menu
8. This is the module you want to be in. Copy and paste the VBA code I gave you under your recorded macro.
9. Make sure that you remove the code from all the other places you may have put it.

Now, it should be able to see it from anywhere in your Workbook.

 

[PocketNeo]

Thanks. The code was not in the Module. I added it and re-ran.

It returns this for date 10/4/2019 9:35:49 AM:

49-48-47-52-47-50-48-49-57-32-57-58-51-53-58-52-57-32-65-77

 

[Joe4]

OK, I don't see any special characters there.
I tried setting up the exact same thing on my side, and it all seems to work just fine on my side:

Using a formula in B2 like =A2-4/24 to reduce 4 hours doesn't work. It returns #VALUE !
I have also tried =DATEVALUE(A2) and it returns #VALUE !

Both those formulas you posted return values for me when I set up my worksheet exactly as you have described.

One last thing to try (which may or may not shed any light on the subject).
What does this return?
=ISNUMBER(A2)