Post

4 followers Follow
0
Avatar

Formula to get week day number (1-7) and current hour (0-23) value?

Hello Clarizen community,

we have been checking the posts as well documentation but were not able to find the function(s) to get a couple of values out of the current date (Now()):

- we need to know the current day number of the week (1 to 7, or "Mon" to "Sun", does not really matter) to make a conditional behavior on a workflow

- we need to know the current hour out of Now() (0 to 23) to make a conditional behavior on a workflow

Please share if you have an idea!

Thanks in advance!

Alexey Grebennikov Answered

Please sign in to leave a comment.

5 comments

0
Avatar

Anyone? Maybe there is some workaround or another approach?

From the business flow point of view what we are looking for is a way to define what are going to be the recipients for the email alert when new issue/bug is posted in clarizen and it should be based on teh day of the week and time of a day.

Thanks.

Alexey Grebennikov 0 votes
Comment actions Permalink
0
Avatar

Hello Agrebennikov,

 

1) This will give you the day of the week as an abbreviation of the weekday name in the string format (feel free to change "Mon", "Tue", etc. values into numbers, but don't forget to replace "Function failed!" with a number as well, otherwise, the function won't work):

Case(
           Mod(Floor(DateDiff(Date, Date(1901, 1, 1), "d")), 7),
           4, "Sat",
           5, "Sun",
           6, "Mon",
           0, "Tue",
           1, "Wed",
           2, "Thu",
           3, "Fri",
           "Function failed!"
)

2) This will give you the value of current time as an integer in the 24h format (e.g. 0, 7, 11, 19, 23, etc.):

ToNumber(Left(Right(ToString(Now()), 8), 2))

 

Please let me know if this answers your question.

Roland Pumputis 0 votes
Comment actions Permalink
1
Avatar

Sorry, forgot to replace one variable (Date >> Today()):

Case(
           Mod(Floor(DateDiff(Today(), Date(1901, 1, 1), "d")), 7),
           4, "Sat",
           5, "Sun",
           6, "Mon",
           0, "Tue",
           1, "Wed",
           2, "Thu",
           3, "Fri",
           "Function failed!"
)

 

Please let me know if this is helpful.

Roland Pumputis 1 vote
Comment actions Permalink
0
Avatar

Thank you Roland,

I was able to leverage your mathematical prowess within a report HIGHLIGHT leveraging the FORMULA options inside of a CUSTOM summary type. Probably too complex for something so simple; but VERY cool for our end users to see a timer for when something is going to run, so thank you...



Wilfredo Maldonado 0 votes
Comment actions Permalink