Using Send hotkeys in excel to enter and copy formulas

Help
1

Hello. I don’t have a clear understanding of how to use the send hotkeys in excel. I’m trying to create formula in cell H2 and also copy that formula to the rest of the rows in that range? Could someone please provide a simplified step by step process on how to execute this?

2

Hi @cgeorge,
Would it work for you to use “Write Cell” activity and use its range? E.g. as shown below:
copy%20formula
Does this help?

3

Hello. Yes, I tried this and it works for simple calculation but I’m actually trying to find the difference between 2 dates in my spreadsheet. Here is what my Write Cell Activity currently looks like:

Capture

dataAsOfD = 8/31/2018
psgD = initial date (varies in each row)
I have them both classified as generic value variable types.

However, when I look at the output it is not performing a calculation. It is just giving me the “dataAsofD” in the new write cell column (8/31/2018).
Can you advise what I’m doing wrong?

4

Hi @cgeorge, thank you for specifying your requirements. Your both variables containing dates should have System.DateTime type. I would add Assign e.g. “datediff (as a generic value - it also works as a double type, but then you need to convert it .ToString in 'Write cell” activity) = dataAsOfD.DayOfYear - psgD.DayOfYear" so it looks like that:

date%20diff%20calculation
date%20diff%20calculation585×798 20.2 KB

Obviously “date.now” was just picked to show my point :slight_smile:

5

Thanks so much! It actually worked! I have two more asks:

  1. This will eventually be a report that will be run monthly based on the previous month’s date (last day of the month - in this instance was 8-31-2018). However, when the report is ran next month it will be based on 9-30-2018. If this will eventually be set, to run automatically via orchestrator on the first day of the next month, how do we set the dataAsOfD to automatically show as the last day of the previous month.

  2. Is there a way to get the datdiff to be more exact with decimals possibly to the 100th place? For instance, instead of the datdiff showing “0”, it could possible show “0.5” to show half of a year.

Thanks again for your help!!

6

Hi @cgeorge,
If you assign your dataAsOfD as “New datetime(now.Date.Year, now.Date.Month,1).AddDays(-1)”, it will be showing you the last day of the previous month. It works for me - the datediff showed -17 as for today :slight_smile:

image
image1075×175 10.2 KB

If this solves your problem, could you mark it, so it shows that this thread contains a working solution? :wink:

As for your second question, the way you have datediff now is that it calculates the difference between the dates in days, so I guess that it would show 0 if the dates are exactly the same. Could you explain how else you would like it to be?

7

Thanks! Nevermind about the 2nd part of my question. I think what I have will work just fine.

1 Like
8

No problem, I am glad that I could help.