# Calculation of date and time difference with 4 number fields

Hi,
I have problem to calculate the time between 2 dates. The issue is that both dates are split between date and time and both are in number format.
So e.g. I have the following:

DateA = 20150220 =>(20.02.2015)
TimeA = 175842 =>(17:58:42)
DateB = 20150223 =>(23.02.2015)
DateB = 100217 =>(10:02:17)

So the result should be: DateB.TimeB - DateA.TimeA = 2days 16hrs 3min 35s

Any idea how to do this?
jezko

jezko (BOB member since 2010-10-12)

Hi,

Just a brief comment:

1. Create 2 report variables of a date type where you will convert DateA and TimeA numbers to a real Date A, as well as DateB and TimeB numbers to a real Date B.
2. Then use those 2 variable and a formula suggested here to get a difference between the dates:
Diff between two dates

Let us know if you get stuck at any point.

Marek Chladny (BOB member since 2003-11-27)

Brief hint:
Convert your date to number of seconds since 1970 for example, convert your time in a similar way, add these two for date 1. Repeat the same for date 2.
Finally subtract those 2 numbers (for dfate 1 and date 2), then parse out the days, hours, minutes.
1 day = 24 * 3600 secoonds fore example

Also, please try a search on BB, this has been discussed and resolved before ; )

Andreas (BOB member since 2002-06-20)

Hi,
how can I convert date and time (2 fields) to a datetime (date format) (1 field)?
e.g.
DateA = 20150220 =>(20.02.2015)
TimeA = 175842 =>(17:58:42)

Thanks.

jezko (BOB member since 2010-10-12)

All having been said, best done at the database side :).

Mak 1 (BOB member since 2005-01-06)

Hi,

Try this formula:

``=ToDate(FormatNumber([DateA];"0") + FormatNumber([TimeA];"0");"yyyyMMddHHmmss")``

Marek Chladny (BOB member since 2003-11-27)

Also shoot whatever idiot decided to store these values this way. That has to be the stupidest method for storing dates and times I think Iâ€™ve ever come across. I think that in order to do anything with those values, you will either have to convert them to strings, parse out the parts, and recombine them into a valid date format, OR do crazy math with FLOOR to separate out the values then re-multiply them by the appropriate values (e.g., 60 for minutes, 3600 for hours) to generate a useful number (e.g., seconds since midnight).

Storing things as numbers that arenâ€™t actually numbers makes baby Jesus cry.

Lugh (BOB member since 2009-07-16)

Itâ€™s similar to having people/employees age as a measure. What for? To calculate the total age of a city/company?

Marek Chladny (BOB member since 2003-11-27)

.

Manipulating / calcuating as numbers, in the ETL code, using Julian etc can be useful, but as Lugh suggests storing them and presenting them to the user to report on, in this format, is just plain wrong

Mak 1 (BOB member since 2005-01-06)

Age as a measure makes sense if you wanna see age distribution across your work force, maybe using percentiles etc.
Better would be length of employment to avoid direct age discrimination.

Otherwise, I agree age is a property of a person, just like hair color, height; or price as an attribute for an item/SKU, etc.

Andreas (BOB member since 2002-06-20)

Thanks all, itâ€™s working OK now!

jezko (BOB member since 2010-10-12)