Formula, Test date 10th of every month for analysis?

Hello,

I require to compare AD reports with HR reports every month for which I have created a repeatable workflow that takes the latest input from the respective folders and perform analysis to provide results of those employees who have been inactive for more than 30 days (Different between last logon date and the date when I perform the test i.e. 10th of every month).

In order to let the workflow automatically calculate the number of days in a given month, the following formula is created

This formula is working well if I use it for the data in the month of March, or when if I use it for April testing with April AD and HR reports (when they send that). However, if Today, I want to validate the results of January and run this formula on the workflow with AD and HR reports for Jan, I get the difference in the number of days between the Last logon date of Jan and 10th of March (instead of 10th of Jan). How can I create a formula that calculate the number of days for the month I am running the analysis for?

Thanks

Date Time

Accepted answers

danilang

Hi @farahhassan4

In your first formula, you have hardcoded DateTimeToday(). You need to change this to a variable input.

Todate('2022-'+DateTimeFormat([InputDate],"%m")+'-10'). For [InputDate] you can either use a workflow constant or have a Date interface tool to enter the value.

Another thing to consider is to change this formula to

Todate(ToString(DateTimeYear([InputDate]))+"-"+DateTimeFormat([InputDate],"%m")+'-10').

This will allow the workflow to produce reports for any year without having to modify the hardcoded year.

Dan

All comments

Qiu

@farahhassan4
I am sorry but I can see the contridition in your statement

(Different between last logon date and the date when I perform the test i.e. 10th of every month).
Vs
between the Last logon date of Jan and 10th of March (instead of 10th of Jan).

So the question is now how can the flow know what is the date should the field "Test Date" in your flow.
then even even when you run the flow on 10th, Mar, the "Test Date" would still have the value of 10th, Jan.
Is there a timestamp or something in the log file we can use?

farahhassan4

Hi there,

So the work flow is designed to get me the difference between the last logon date as per the AD report and the 10th of a given month. For example, if I receive AD report and HR in the month of March the workflow will give me the difference between the last logon date as indicated in the AD report and the 10th of March, which is good. The same pattern will follow in the next months.

However, just imagine if HR comes to me and ask me to run the workflow for the the month of Jan, 2022 on the AD and HR reports provided to me in Jan so I want the workflow to provide me the difference between the last logon date for employees indicated in the AD for the month of Jan and the 10th of Jan.

The above is the desired result.

You are right how should the workflow know which date should be the Test date? I am thinking if there is any way that the workflow runs the logic based on the "LastWriteTime" of the files?

danilang

Hi @farahhassan4

In your first formula, you have hardcoded DateTimeToday(). You need to change this to a variable input.

Todate('2022-'+DateTimeFormat([InputDate],"%m")+'-10'). For [InputDate] you can either use a workflow constant or have a Date interface tool to enter the value.

Another thing to consider is to change this formula to

Todate(ToString(DateTimeYear([InputDate]))+"-"+DateTimeFormat([InputDate],"%m")+'-10').

This will allow the workflow to produce reports for any year without having to modify the hardcoded year.

Dan

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703