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!

 

[PocketNeo]

Lol, thats why i am so stumped!
It returns FALSE.

 

[PocketNeo]

Just so we're talking apples to apples in re-testing this...
1) I copied this exact date into a brand new workbook/worksheet in Excel 365, in cel A1 which has format GENERAL: 10/4/2019 9:35:49 AM.
2) in cell B1, which is also type GENERAL, I put formula =A1-4/24, which returns #VALUE !

Are you saying that your test returns a value and not an error?

Just so you know I tried a diff date format manually and that worked.
04-Oct-2019 09:20:01 AM

This may prove that the date I have above is just not being recognized as a date....thats what i deduce.

 

[Joe4]

It returns FALSE.

OK, so it is definitely a text entry.

Are you saying that your test returns a value and not an error?

Yes.

All those things should work on it. I am mystified.
What happens if you try:
=A2+0

If you open up a brand new workbook, and type the date in as Text (do NOT use copy/paste, as you might be bringing over the weirdness), and then try the formula:
=A2+0
does that work then?

The only thing I can think of is that there is something funky with that particular workbook.
Did you create it, or did it come from somewhere else?

 

[PocketNeo]

I did that, entering it all manual and used the formula +0 and same # VALUE !

I believe that Excel does not recognize this date in this format. Very weird

10/4/2019 9:35:00 AM

When i try other formats then its fine.

 

[Joe4]

I am curious if it has its date formats regional settings changed to a European one or something like that.

Try entering these two values, exactly as-is, in any column formatted as General, and let me know what each returns:
10/14
14/10

 

[PocketNeo]

Both return 14-Oct

 

[PocketNeo]

I played with my date and time value in A1, and the simple formula =A1+4/24. The error I got only happens when the time value exists.
So, 10/4/2019 returns a valid result in the above formula, and 10/4/2019 9:25:00 AM returns the error.

Then, I then played with my Windows 10's regional setting (date and time) formats.

Here's what I have now...

Short Date: dd-mmm-yyyy
Long Date: dd-mmm-yyyy
Short time: hh:mm tt
Long time: hh:mm:ss tt

So...this worked
- using this value in A1: 04-Oct-2019 09:20:01 AM
- using this formula: =A1-4/24
- Returns this result: 10/04/2019 05:20:01 AM

The PC's regional settings has a direct affect on date and time display in Excel.
IMHO, it should not. But Im not going to fight that battle!

 

[Joe4]

When you have the full date and time, does it work?

Yes.

Here is a little macro that I created that enters the value in cell A2, and formulas in B2 and C2.
B2 and C2 return values for me with no errors.
Try running this macro in your file, and see what happens.

Sub Test()
    Columns("A:A").NumberFormat = "@"
    Range("A2") = "10/4/2019 9:35:49 AM"
    Range("B2").Formula = "=A2+0"
    Range("C2").Formula = "=A2-4/24"
    Range("C2").NumberFormat = "m/d/yyyy h:mm:dd AM/PM"
    Range("A:C").EntireColumn.AutoFit
End Sub

Do B2 and C2 show values or errors?

What regional setting do you have?

My regional settings look like what is shown in Step 3 here: https://edu.gcfglobal.org/en/excel-tips/understanding-regional-differences-in-excel/1/, with one exception.
My "Long Date" format is: dddd, MMMM d, yyyy

Check closely to see if your time settings match what is shown in the link I referenced above.

 

[PocketNeo]

Your regional settings are now exactly what I have now. I changed my short date to M/dd/yy, which seemed to fix all of the issues for this spreadsheet.
Prior to this, with the short date as "Short Date: dd-mmm-yyyy" in my last post, caused Excel to not recognize the month.

What I have found and worth noting from all this is that your regional settings assumes the date data you have in ALL your spreadsheets are uniform. We know this is not true.
In my case, the date/time came from a report .CSV dump from a foreign platform. Their date/time format is proprietary to their software. And when opening in excel, displays as its provided. Only when I play with it (i.e. removing 4 hours of time) does it start to become problematic for me. Had the date/time been provided to me in a format recognized by my regional settings, we would never be having this thread.

Case in point, Excel should have the ability to set, globally, within a workbook and/or worksheet, the date format (override?) to interpret what you are viewing....would have helped me a ton!

Have a great weekend and thanks again.

 

[Joe4]

You are welcome.
Glad you got it all sorted out!