Create Excel file that checks today date in column

Help Studio
, , ,
#1

Hello,
I need help I want to create a function where an Excel file with date column (A1) and time column (B1) is called and then checks if today’s date is already entered in column A1. If yes, the function should end, if no, it should enter today’s date and the current time.

Can anyone help me with this? Thank you!

#2

Hi @kowdfimls23 ,

Maybe some more detail would help us understand your requirement clearly.

Do you want to Check in the whole Column A or Do you want to just check in Cell A1 ?

Also, if Today’s Date is not found, In which cell would you want to enter the Today’s date value ?

#3

Hi, yes exactly, it should check in the whole column A if today’s date is already there. And if it’s not found, then it should be added in column A under the last inserted item (like e.g. the date from yesterday).

#4

@kowdfimls23 ,

Maybe you could try out the below Steps :

  1. Read the Excel Sheet Data using Workbook Read Range Activity. Get the Output as Datatable, say DT.

  2. Find if there is a Date corresponding to Today’s Date using a Linq like below :

Not(DT.AsEnumerable.Any(Function(x)CDate(x(0).ToString).Date =Now.Date))

  1. You could use the above Expression in an If Activity and in the Then Block you could use an Add Data Row Activity and write Today’s Date value.
    image

  2. You could then write the Updated Datatable back to Excel Sheet using a Write Range Activity.

Or

Instead of using Add Data Row and writing the updated Datatable back to Excel sheet, you could also use Write Cell Activity and write the Today’s Date value in the last cell of A Column using Datatable’s Row Count.
image

Let us know if you are unable to perform any of the steps.

1 Like
#5

It worked, thank you!!
But what do I have to do, so that it also enters the current time in column B next to the date at the same time? And is it possible to also add a comment like “Executed successfully” in column C?

#6

@kowdfimls23 ,

Could you let us know which method have you adopted from the above suggested ?

If Write Cell Activity is your preferred method, then similarly you would need to use two more Write Cell Activities to write the data you want to write, specifying "B"+(DT.Rows.Count+2).ToString in the Cell Range Property for writing the Date value in Column B and "C"+(DT.Rows.Count+2).ToString for writing the value Executed successfully.

Let us know if you still need help.

1 Like
#7

I chose the write cell method
It worked perfectly - thanks again for your help!!

1 Like
#8

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.