Adding Hours to a Date

PocketNeo

Board Regular
Joined
Jun 10, 2002
Messages
139
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,416
Office Version
365
Platform
Windows
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

Board Regular
Joined
Jun 10, 2002
Messages
139
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,416
Office Version
365
Platform
Windows
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?
 
Last edited:

PocketNeo

Board Regular
Joined
Jun 10, 2002
Messages
139
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,416
Office Version
365
Platform
Windows
Place this User Defined Function I created in a VBA module in your worksheet.
Code:
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)
 
Last edited:

PocketNeo

Board Regular
Joined
Jun 10, 2002
Messages
139
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.
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,416
Office Version
365
Platform
Windows
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

Board Regular
Joined
Jun 10, 2002
Messages
139
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,416
Office Version
365
Platform
Windows
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)
 

Forum statistics

Threads
1,081,513
Messages
5,359,227
Members
400,523
Latest member
ExcelNewbie98

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top