Mod function

[bta1322]

Hi

Can anyone help
Trying to use the mod function in Business Objects (end user web intelligents)
= mod([call-over date];1) but keep getting error
callover date example - 04/4/2011 09:21:13

trying to sperate date and time into diffent columns as variables

Help / direction appriciated

Regards

Brian

 

[hilfy]

You can't use Mod to do this. Try looking at the FormatDate function - use the format string to give the date (MM/dd/yyyy) in one formula and the time (h:mm:ss) in the other.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[bta1322]

thanks hilfy for your support

have followed suggestion, now got two colums, one formatted as date and the other as time.
Column one -[Available to Despatch] (Date Format)
Column two -[Available to despatch] (Time format) - i have named this column diffent to the other (called available time)

I'm trying to get a point, where if the time is greater that 16:00:00 in column two, then one day is to be added to column one

cannot get required result, tried
=If([Available Time]) > '16:00:00' Then "yes" but this populates all cells with "Yes"

where am i going wrong?

 

[hilfy]

Try this:

If [Available Time] > Time(16, 0, 0) then [Available Date] + 1
else [Available Date]

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[bta1322]

Hi Hifty

Thought this looked good, however, getting Invalid indentifyer "TIME"

 

[hilfy]

I don't have Crystal on the computer where I'm working, so I can't get to the help docs. In Help, do a search on Time to see if you can find the function where you can convert numbers to a time.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[hilfy]

Or you may have to convert your Available Time to a string in order to do a comparison.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[bta1322]

Hi Hilfy

Convert available time to string? Is that format as a number? Tried to do that and go Format error. How do i format to string?

 

[hilfy]

What is the exact error that appeared? What is your available time formula?

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[bta1322]

This is the variable formula in both AVAILABLE DESPATCH and AVAILABLE TIME columns

=If([Date Last Hold Removed (all Holds)])='01/01/3000' Then ([Call over (UK)]) ElseIf [Call over (UK)]<[Date Last Hold Removed (all Holds)] Then [Date Last Hold Removed (all Holds)] ElseIf ([Call over (UK)])>[Date Last Hold Removed (all Holds)] Then ([Call over (UK)]) ElseIf ([Call over (UK)]=[Date Last Hold Removed (all Holds)]) Then( [Call over (UK)])

One is formated as DD/MM/YYYY and the other in time HH/MM/SS

Another column added with the variable
=If([Available for Dispatch]) > Time (16, 0, 0) then[Available for Dispatch] + 1
else [Available for Dispatch]

but getting Invalid identifier 'Time' at position 32. (WIS 10022)

 

[hilfy]

For some reason I was thinking in terms of this being in Crystal instead of Webi. Sorry!

Try this:

=If([Available for Dispatch]) > "16:00:00" then[Available for Dispatch] + 1 else [Available for Dispatch]

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[hilfy]

I didn't mean to submit that quite yet.... You'll probably have to call the function to convert [Available for Dispatch] to a string prior to doing the comparison. You want to make sure to use the format string that will left-pad the hour with a zero (0) if the time is less than 10:00.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[bta1322]

ok,

i am a newbie !

how do i -
1. convert [Available for Dispatch] to a string prior to doing the comparison.
2.You want to make sure to use the format string that will left-pad the hour with a zero (0) if the time is less than 10:00.

 

[hilfy]

Try this:

=If(FormatDate([Available for Dispatch];"HH:mm:ss") >= "16:00:00";[Available for Dispatch] + 1; [Available for Dispatch])

(I was accidentally mixing Webi and Crystal syntax in my original posts - this one is purely Webi)

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[bta1322]

Hi Hilfy

It seems to be the "1" (without quotes) that is being highlighted as error


Keep getting error :-
The expression or sub-expression at position 95 in the '+' function uses an invalid data type. (WIS 10037)

=If(FormatDate([Available for Dispatch];"HH:mm:ss") >= "16:00:00";[Available for Dispatch] + 1; [Available for Dispatch])

 

[bta1322]

incidentally, the formula does work without the +1

 

[bta1322]

Hi Hilfy

I have been playing with your formula and tried

=If(FormatDate([Available for Dispatch];"HH:mm:ss"))>="16:00:00" Then RelativeDate([Available for Dispatch]; +1)

This does seem to be giving me my required results

Thank you for you patience

Regards Brian