How to convert times in deski

building reports desktopintelligence
1

Hi,

I’m working with a few databases where the times and dates consist of numbers.

The dates isnt a problem anymore, see below;

Date:
=RelatieveDatum(‘28-12-1800’ ,())

Year
=Jaar(RelatieveDatum(‘28-12-1800’ ,()))

Month
=Maand(RelatieveDatum(‘28-12-1800’ ,()))

I now need to convert times (see attachment) which appaers to be a bit less straightforward then i thought.

Anyone woth experience on this subject?
Test tijden omzetten uit Globe.xls (90.0 KB)

tantetruus (BOB member since 2008-11-11)

2

Sorry, not allowed to open non-work spreadsheets here.

Can you post an example please?

3

Hi Mark,
You mean an example like this?

Number Time
4200001 11:40
7110001 19:45
4110001 11:25
4200001 11:40
3690001 10:15

tantetruus (BOB member since 2008-11-11)

4

Kind of.

I guess the question I should have asked is…

What does the data that you are starting with look like and what do you want to do with it?

5

ok,
the numbers on the left is the (Clarion) time in which it is found in the database.

The numbers on the right are the “real world” times after conversion as they should be

tantetruus (BOB member since 2008-11-11)

6

OK.

Clarion times are defined as 1/100s of a second past midnight, plus 1.

So, to get from 4200001 to 11:40, you’d take the following steps:
Substract 1
Divide by 100
This gives you the number of seconds past midnight. So, create your variable Sec After Midnight as:
=(-1)/100

Then use that to get the time, create a variable called Time String, defined as:
=FormatNumber(Floor(/3600)*100+((Mod(;3600))/60),“00:00”)

The time above is formatted as a string, so if you want it as a date, use =ToDate(,“HH:mm”)

7

i somehow missed your answer, thx!

tantetruus (BOB member since 2008-11-11)