Calculation of date and time difference with 4 number fields

system · February 23, 2015, 9:05am

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?
Thanks a lot in advance!
jezko

jezko (BOB member since 2010-10-12)

system · February 23, 2015, 9:29am

Hi,

Just a brief comment:

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.
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)

system · February 23, 2015, 9:31am

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)

system · February 23, 2015, 10:27am

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)

system · February 23, 2015, 10:46am

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

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

system · February 23, 2015, 10:50am

Hi,

Try this formula:

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

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

system · February 23, 2015, 3:19pm

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)

system · February 23, 2015, 3:30pm

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)

system · February 23, 2015, 3:33pm

.

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)

system · February 23, 2015, 3:37pm

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)

system · February 24, 2015, 8:46am

Thanks all, it’s working OK now!

jezko (BOB member since 2010-10-12)