Write cell in excel if time is greater than 8 hours

Help Studio
,
#1

Hi everyone!
I’m having trouble with an if condition activity for excel.

I have a column named “TIME” which is in time date type like “HH:mm:ss”.
time
I want to check if the time is bigger than 8 hours and if it is, to write in a cell if it’s “violated” or “not violated”
Can anyone help me out with this???

Thank you in advance!
Chrissy

#2

Suppose CellTime contains the data scraped from a cell of the spreadsheet.

Use an If activity with condition: CInt(CellTime.ToString.Split(":"c)(0) + CellTime.ToString.Split(":"c)(1) + CellTime.ToString.Split(":"c)(2)) > 80000. If it returns true, write “violated”, else “not violated” to the cell where you want this to be placed.

2 Likes
#3

Hi Anthony,

Thank you for your help.
I tried what you suggested but there seems to be a mistake. Can you please have a look below?
Thanks!

scrn1
scrn11606×881 42.7 KB

1 Like
#4

Check the value of time when this error happens. I suspect that there is an entry with less than two :'s in it.

#5

This is the excel file I’m using. All the entries are in the form of HH:mm:ss
results1.xlsx (11.0 KB)

Would it help if I uploaded the .axml file?

#6

Step through the process in Debug mode, and find which value is throwing the error. If it is happening on the first entry, I want to see what time is set to in the Locals tab while you’re running the project in Debug mode just to be sure what data is in the variable. I have a suspicion that there is more to what’s in the field than what I can see in the spreadsheet.

#7

The output message says “If: Index was outside the bounds of the array.”
And it shows the problem to be in the “if” activity.

condition.xaml (12.0 KB)
This is my axml file. It’s a little more complex than just the time thing, but I don’t have any issues with the other “if” activity. The excel file is exactly like the one above.

If you could take the time to help my out I’d appreciate it so much! :slightly_smiling_face:

#8

The condition is failing in the If statement because something unexpected happened when building the string using time.ToString.Split(":"c). Log this to the output and let us know what the messages say.

Log message 1: time.ToString.Split(":"c)(0)
Log message 2: time.ToString.Split(":"c)(1)
Log message 3: time.ToString.Split(":"c)(2)
Log message 4: time.ToString

1 Like
#9

sorry, but I don’t know how to do that…

#10

Something like this, but the log messages will need to come before your process reaches the If activity.

image
image1594×856 46.1 KB

1 Like
#11

Hi @1webplace

Check below workflow

8hours calc.xaml (6.6 KB)

Hope this helps you

Thanks

2 Likes
#12

Thank you so much!!
I’ll try it and I’ll let you know, probably by Monday. :grinning:

.

#13

I like this solution, but please note that you need the package UiPath.Python to run this workflow. Not sure why, though, since the syntax just looks like Vb.Net.

1 Like
#14

Hey @Anthony_Humphries

Sorry i was working on some other flow, python package is not required if it is prompting

Thanks for letting me know i will update anyother one

Thanks

1 Like
#15

Hi @Anthony_Humphries

Updated the workflow without python packages

Thanks

2 Likes
#16

Hi!
Thank you very much for taking the time to help me out. Both you and @Anthony_Humphries !
I’ve tried your .xaml and I think we’re almost there.

The entries of time are in the form of HH:mm:ss , so I think the “totalTime >= 28800” isn’t exactly right because I’m always getting the same result. Would you be so kind and have a look at it?
Thank you!
I’m uploading my version of the .xaml you’ve sent me 8hours calc_ver2.xaml (13.0 KB)

#17

Hi @1webplace

Check below screen, for the error

image

The highlighted “Time” Should be your excel cell which is having the time, not the default time

Thats why it is always getting less than 8 hours

Hope this helps

Thanks

#18

I’ve tried that but I got the following error and then changed it back to your deafult value

scrn2
scrn21920×1200 146 KB

#19

@1webplace

Try this workflow, In the IF condition you can place your condition

Main.xaml (8.5 KB)

Thank you

1 Like
#20

OMG!!! That’s it!
You’re the best!

Thank you so much! :star_struck:

1 Like