Two seperate dates with time stamps

[Former Member]

I have two different dates with time stamps. I am trying to figure the hours between the two dates with the time stamp included. Do I use the same formula below except insert the time stamp and get that number and then take the difference between the two different formulas?

Digitization time is the start time and comp time is the completion time.

=-1*(DaysBetween([Derivatives Processed per Hour by Workflow].[Digitization Time];[Derivatives Processed per Hour by Workflow].[Comp Time])*86400+(ToNumber(FormatDate([Derivatives Processed per Hour by Workflow].[Digitization Time];"HH"))*3600+ToNumber(Left(FormatDate([Derivatives Processed per Hour by Workflow].[Digitization Time];"mm;ss");2))*60+ToNumber(FormatDate([Derivatives Processed per Hour by Workflow].[Digitization Time];"ss")))-(ToNumber(FormatDate([Derivatives Processed per Hour by Workflow].[Completion Full Date];"HH"))*3600+ToNumber(Left(FormatDate([Derivatives Processed per Hour by Workflow].[Completion Full Date];"mm;ss");2))*60+ToNumber(FormatDate([Derivatives Processed per Hour by Workflow].[Completion Full Date];"ss"))))

[mhmohammed]

Hi Roger,

Check out this discussion, I've tried it before suggesting and it works great.

Thanks,
Mahboob Mohammed

[Former Member]

Thank you for your reply. The date field and the time stamp field are two different columns in my report. Can I still use the formula from the thread you recommended?

[mhmohammed · Accepted solution]

Hi Roger,

You'll have to create a Date Time field first which concatenates those 2 columns (Date and Time) and converts them to a date with timestamp using todate() function.

Question: I assume the date field is of data type Date. What about the time stamp, what is the data type of that field?

Can you please send us a screenshot?

Thanks,

Mahboob Mohammed

[Former Member · Accepted solution]

I have attached a screen shot of the table.

Thank you very much

[Former Member]

Roger,

Create a variable , concatenate the Digitization completion date and time .

Then use the formula ToDate([Variable];"dd-MM-yyyy HH:mm:ss") to convert it to datetime format.

Then use the formula as mentioned in the other thread

Regards

Sri Harsha

[mhmohammed]

Hi Roger,

You missed out on one specific question I asked, what is the data type of that object completion time?

Another question: What kind of database is it?

Thanks,

Mahboob Mohammed

[Former Member]

Hi, Mahboob

I am going to have to talk with the engineer that created the universe that I am using business objects to create the report from. I am not certain. I will ask about the database also. Sorry, I didn't create the universe.

Roger

[mhmohammed]

Hi Roger,

Even without talking to the Engineer who created the Universe, what do you see when you drag the mouse over Comp Time object and leave it for a sec? It is supposed to show the data type of that object. Check out the below picture. This is what I see when I drag mouse over and leave it there for a sec. It shows that the Type is Date.

I tried to recreate your issue by concatenating Comp Date & Comp Time objects together to create a Comp Date Time object and format it to show Date with Timestamp.

I created the below objects with sample hard coded date and time:

• Comp Date =Todate("10/30/2015";"MM/dd/yyyy")

This results in an object whose data type is Date, but when I drag it in the report and format to display including a timestamp, it shows as 10/30/2015 12:00:00 AM by default. That's an issue.

• Comp Time =Todate("12:30:59";"HH:mm:ss")

This results in an object whose data type is Date, but when I drag it in the report and format to display as timestamp, it shows as 1/1/1970 12:30:59 PM, it added that date by default as I didn't supply any. That's another issue.

Lets forget about the issue that I'm having in recreating them. Would you please create an object to concatenate Comp Date and Comp Time with the below formula, and let us know what is the data type of the result object? And what do you see when you drag it in the report?

1. v_Comp Date Time 1 = [Comp Date] + " " + [Comp Time]
2. v_Comp Date Time 2 = Todate([v_Comp Date Time 1];"MM/dd/yyyy HH:mm:ss")

Thanks,

Mahboob Mohammed

[Former Member]

Oh, ok, when I hold the mouse over the object it tells me it is a text.

Sorry I misunderstood.

Roger

[Former Member]

Hi, Mahboob,

I went into the query and held the mouse for a second and it said it was a string.

Roger

[mhmohammed]

Thanks Roger.

Create the below 2 objects

• Digitization Completion DateTime (this will give you this date with a timestamp)

=Todate([Digitization Completion Date]+" "+[Digitization Time];"MM/dd/yyyy HH:mm:ss")

• Completion Full DateTime (this will give you this date with a timestamp)

=Todate([Completion Full Date]+" "+[Comp Time];"MM/dd/yyyy HH:mm:ss")

Now, we've the 2 dates with timestamps, let's find the duration between them in hours. Create the below variables

• Duration in Seconds

=DaysBetween([Digitization Completion DateTime];[Completion Full DateTime]) * 86400

+

(

ToNumber(FormatDate([Completion Full DateTime];"HH")) * 3600 +

ToNumber(Left(FormatDate([Completion Full DateTime];"mm:ss");2)) * 60 +

ToNumber(FormatDate([Completion Full DateTime];"ss"))

)

-

(

ToNumber(FormatDate([Digitization Completion DateTime];"HH")) * 3600 +

ToNumber(Left(FormatDate([Digitization Completion DateTime];"mm:ss");2)) * 60 +

ToNumber(FormatDate([Digitization Completion DateTime];"ss"))

)

Finally

• Duration in Hours (hh:mm:ss)

=FormatNumber(Floor([Duration in Seconds]/3600);"00") + " hr(s) " + FormatNumber(Floor(Mod(Mod([Duration in Seconds];86400);3600)/60);"00") + " min(s) " + FormatNumber(Mod(Mod(Mod([Duration in Seconds];86400);3600);60);"00") +" sec(s)"

Let me know if that works.

Thanks,

Mahboob Mohammed

[Former Member]

Hi, Mahboob,

Thank you very much for all your help. I will give it a try and let you know.

Thanks,

Roger

[Former Member]

Hi, Mahboob,

The following formula just gives me a date not time:

=ToDate([Derivatives Processed per Hour by Workflow].[Completion Full Date]+" "+[Derivatives Processed per Hour by Workflow].[Comp Time];"MM/dd/yyyy HH:mm:ss")

What would I be doing wrong?

Roger

[Former Member]

Hi, Mahboob,

when I use this formula =ToDate([Derivatives Processed per Hour by Workflow].[Digitization Completion Date]+" "+[Derivatives Processed per Hour by Workflow].[Digitization Time];"MM/dd/yyy HH:mm:ss")  I get an #ERROR

Thanks,

Roger

[mhmohammed]

Hi Roger,

What is the data type of the object [Digitization Completion Date]?

Thanks,
Mahboob Mohammed

[Former Member]

Hi, Mahboob,

Digitization Completion Date = Type: Date Time

Completion Full Date = Type: Date Time

Thanks,

Roger

[Former Member]

Hi, Mahboob,

Attached you will find the query that i wrote.

Thanks,

Roger

[mhmohammed]

Hi Roger,

You're answered the question yourself. As you said, Digitization Completion Date is DateTime, it seems there's already a timestamp attached to it and that's why the new object gives you an #ERROR. Let's remove that default timestamp by creating a new variable for Digitization Completion DateOnly using the formula

=Formatdate([Derivatives Processed per Hour by Workflow].[Digitization Completion Date];"MM/dd/yyyy")

Then, update the Digitization Completion DateTime (which gave you an #ERROR earlier) to use this new object as below

=[Digitization Completion DateOnly]+" "+[Derivatives Processed per Hour by Workflow].[Digitization Time];"MM/dd/yyy HH:mm:ss")

Makes sense? Let me know how it goes.

PS: I don't have to look at the query.

Thanks,
Mahboob Mohammed

[Former Member]

Hi, Mahboob,

It worked great, thank you so very much, I really appreciate it.

Roger

[mhmohammed]

Great! Please work your way through, per my response to create Completion Full DateTime, Duration in Seconds and Duration in Hours.

Let me know how it goes.

Thanks,

Mahboob Mohammed

[Former Member]

Hi, Mahboob,

I am so sorry, I don't mean to bother you.

The formula: =[Completion Full DateOnly]+" "+[Derivatives Processed per Hour by Workflow].[Comp Time];"MM/dd/yyyy HH:mm:ss")

Is giving me an error. I am missing a parentheses. No matter where I put it I still get an error. The error I get is "Invalid Character"

Thanks,

Roger

[mhmohammed]

Hey Roger,

My bad, I missed the Todate() function (the text in bold). What we were trying to do is, create a Completion Full Date with timestamp which needs a Todate() function, as we did to create the Digitization Date with timestamp.

This is what it should be

=Todate([Completion Full DateOnly]+" "+[Derivatives Processed per Hour by Workflow].[Comp Time];"MM/dd/yyyy HH:mm:ss")

Try that and let me know, what happens.

After this, you should have the 2 different dates with timestamps and you'll just have to create Duration in Seconds and Duration in Hours.

Thanks,

Mahboob Mohammed

[Former Member]

Hi, Mahboob,

If I use the formula: =[Completion Full DateOnly]+" "+[Derivatives Processed per Hour by Workflow].[Comp Time] I get the correct answer.

If I use: =Todate([Completion Full DateOnly]+" "+[Derivatives Processed per Hour by Workflow].[Comp Time];"MM/dd/yyyy HH:mm:ss")

I just get the date and not the hours.

Thanks,

Roger

[mhmohammed]

Hey Roger,

Yeah, but the data type of output of the first formula you have will be a String which doesn't work with Daysbetween formula that you eventually need. Use the 2nd formula and then try to Format Number on that column to show Date with a Time Stamp.

Steps:

Right click on that Completion Date Time column -> select Format Number -> click on Date from the list of Left and on the right, select a Date that has a timestamp (I hope it should be 2nd from last), click Apply, OK. You should see the date with a timestamp.

Let me know what happens.

Thanks,

Mahboob Mohammed

[Former Member]

Hi, Mahboob,

That worked. Now I will try the other two formulas to work my way through Seconds and Hours.

Thanks,

Roger

[mhmohammed]

Great, keep me posted.

Thanks,

Mahboob Mohammed

[Former Member]

Hi, Mahboob,

It worked! thank you very much. I really appreciate it.

Thanks,

Roger

[mhmohammed]

Glad, I was able to help.

Thanks,

Mahboob Mohammed