>

Formulas in Excel reports

Answered
Pieter Fourie

Here is a basic question, not an issue :
I'm working on custom fields and I would like to know how the time format is set in USI.
I'm setting an IF Excel formula and would like to integrate the Start Event field; should I write :
=IF({Start Event}>07:00,"full day","end of night event").

It is not the correct formula, but I wanted to show you something in order to ensure your understanding of my question, which is  :

--> What is the time format ?

  • "00:00" (with the quotation marks)"
  • 00:00 (but it does not work)
  • 0000
  • ...

The aim is simply to set the formula to provide an information depending on a specific timing.
I'm not a rooky on formula, reason why I don't ask for a specific development.

Since the beginning of 2023, and due to the increase of energy cost, we are charging our customers with a fee, calculated on the surface rent and the duration in days.

I have created 2 formulas, as due to some specific cases, one will not count all information.

And here is the precise reason of my request : Some customers rent a surface from 18:00 o’clock to 02:00 in the morning the next day.

My formulas, based on the surface and the number of days, will suggest the Event Manager to charge 2 times the surfaces (as the system will consider 2 days).

But the willingness is to only charge the customer on one day.

So, my willingness is to add a parameter in my “IF” formula that will react as so :

If the event starts at 07:00 in the morning, than it has to be taken into consideration.

Or “formula speaking” : IF({start Event}>0700,1,0)

 I’m sure that if this is not working I can take time to think about; there might have another way to integrate a parameter in the formula.

Comments

7 comments

Date Votes
  • Josiane Mikhael

    Client:  EPFL

    ZD: 288747

    There are no KB articles regarding this case 

    Sara Noonan Seth Halvaksz @...  can we please have an answer on this one, and if further info is needed please let us know 

  • Seth Halvaksz

    Tagging Richard Marmura to help with this question

  • Josiane Mikhael

    Richard Marmura Seth Halvaksz any ideas please? 

  • Pieter Fourie

    Morning Team

    I would rather not repeat what the customer said in https://ungerboeck.zendesk.com/agent/tickets/288747, but not receiving any insights from anyone here, is a bit alarming…

    Josi, could we add Mike to the case as well…& anyone else that might have some ideas on this..?

  • Josiane Mikhael

    @... @...  I think now we have the full team, any ideas if what the client is asking is possible on a report? 

  • Mike Johnson

    I will admit I'm not an Excel formula wiz but I was able to get this to work doing the following:

    =IF({Start Time}>TIME(7,0,0),1,0)

    (7,0,0) ; 7 = hours, 0 = minute, 0 = seconds

    I was doing this in the Events grid.

  • Pieter Fourie

    Good morning Mike

    Thank you for the input.

    I will run this past the customer & let you know if it's the answer.

    Rgds

    P

Please sign in to leave a comment.

Didn't find what you were looking for?

New post