PDA

View Full Version : Time formula



samagonas
04-26-2018, 02:41 AM
Hi all,

I need your help. So I have specific times from which I need to subtract 6hours. Everything would be okay but when it goes past midnight it messes up. Example:

Let's say I have 20:00 in A1 and in A2 I have =A1-6/24 which would be 14:00, but if it was 00:10 in A1 and same formula in A2 it won't calculate.

I'm sure there is easy solution to this, but I can't seem to find it. I have attached screenshot if you can't understand me.

Thanks for help!

SamT
04-26-2018, 08:26 AM
A User Defined Function sounds easiest to me. A UDF is used in Formulas, just like all Excel Functions.

You can use the "Insert Function" system of Excel, Click the fx icon, and specify Category "User Defined."


Place this code in a Standard Module in the Workbook
Option Explicit

Public Function Subtract6Hours(OriginalTime As Range) As Date
Dim Tmp As Date

Application.Volatile
Tmp = CDate(OriginalTime.Value)
Subtract6Hours = DateAdd("h", -6, Tmp)
End Function



In Cell B1, use this Formula

=Subtract6Hours(A1)

Paul_Hossler
04-26-2018, 01:59 PM
SInce you're getting ########### instead of just the wrong calculation, I'm guessing that

a. 00:00:00 is being treated as a string and not being internally converted to a time, OR

b. there is no Date component in the cell, and therefore it's midnight of the the earliest Excel date and 6 hours before that is undefined


Personally, I suspect b) since this way seems to work

22110



Post a small workbook with enough data to show the issue if you want