May 9, 2023, 3:28pm
This is part of my process I’m trying to figure out:
Obtain when the process was last run by referring back to a datetime variable (dtLastRun) which had Now assigned to it
(this would be done at the end of the last run of the process)
Download a spreadsheet, work through the column where the survey date is featured, saving the variable as strSurveyTime
Work out if the survey was returned since the process was last run
Issues I’m having:
The dtLastRun is in the American format, i.e. today is 09/05/2023 16:21:12 but the dtLastRun is bringing back 05/09/2023 16:21:12.
How would I change this into the UK format?
The strSurveyTime is bringing back the UK format, i.e. 09/05/2023 16:21:12 and is a string format.
How can I convert the string into a DateTime variable?
May 9, 2023, 3:32pm
DateTime.ParseExact(strDate, "MM/dd/yyyy HH:mm:ss", CultureInfo.InvariantCulture).toString("dd/MM/yyyy HH:mm:ss")
Hope this may help you
Please try the following expression for your use case:
dtLastRun = DateTime.ParseExact(dtLastRun.ToString("MM/dd/yyyy HH:mm:ss"), "MM/dd/yyyy HH:mm:ss", CultureInfo.InvariantCulture).ToString("dd/MM/yyyy HH:mm:ss")
Hope this helps,
May 9, 2023, 3:37pm
Thanks for getting back to me so quickly.
DateTime.ParseExact(strDate, “MM/dd/yyyy HH:mm:ss”, CultureInfo.InvariantCulture).toString(“dd/MM/yyyy HH:mm:ss”)
With this, it looks like it’s converting to string so would I still be able to compare the time passed between dtLastRun and dtSurveyTime? (Cdate(yourStringFormat) worked so I’m now using the datetime variable instead of strSurveyTime, thank you!)
Maybe also check with the below Expression :
DateTime.Parse("09/05/2023 16:21:12",new System.Globalization.CultureInfo("en-GB"))
May 9, 2023, 3:45pm
With this, it looks like it’s converting to string so would I still be able to compare the time passed between dtLastRun and dtSurveyTime?
May 9, 2023, 3:46pm
Thanks for the response
That looks like it’s converting the time to string, will I still be able to work out the difference between the dates?
It is in the DateTime type and not a String.
May 9, 2023, 4:06pm
The date and time will always change though, so I can’t quote manually 09/05/2023 16:21:12 in it and it isn’t working with a variable
Is it possible for you to showcase what you have implemented or tested ?
And we should also be able to use a variable in place of the String value used.
Showcasing of visual implementation and testing done would get rid of the confusions that are present and would allow us to correct our/your findings faster.
May 16, 2023, 1:58pm
Apologies, for some reason one of my work laptops won’t let me upload pictures from it to the Forum, I’ve had to faff around and email the screenshots to my other one.
So I’ve tried someone else’s suggestion of using CDate(strVariable) to convert Now into DateTime but I get this error:
With the converting the date cell in Excel to DateTime isn’t working either, this is what it’s picking up:
And this is it after I’ve used your code:
image1346×708 132 KB
Was this not the Expected result ? From the Output Panel, we do see that the format is changed.
May 16, 2023, 2:46pm
So it originally picked it up in the correct format (SurveyTime org) but when I’ve converted it into a DateTime variable (dtSurveyTime, the second of the write lines) it’s converted back to the US format. It needs to be in DateTime variable format because I need to compare it to another DateTime variable to see if the survey was received after a certain time
May 16, 2023, 2:53pm
For Comparing Now UK Time: UTC+1:00 and String that you receive from Excel:
you can create 2 variables:
NowTime: DateTime.UtcNow.AddHours(1).ToString(“dd/MM/yyyy hh:mm:ss”)
ExcelDateTime: DateTime.Parse(strSurveyTime,new System.Globalization.CultureInfo(“en-GB”))
and compare both of them in an if activity.
Is it possible for you to show the other DateTime variable and value in the Output Panel by logging as well ?
We’ll be able to compare the values better.
May 16, 2023, 3:32pm
Just tried that:
It’s still converting back to US formatting when changing to a DateTime variable
May 16, 2023, 3:34pm
I’m trying to change the Now.ToString(“dd/MM/yyyy hh:mm:ss”) to a DateTime variable but it’s not recognising today’s date as a valid date as there’s no 16th month
May 16, 2023, 3:35pm
I see that they both are coming in different formats,
But if you wanna compare them to each other, can you try that once?
I think so that you’ll be able to compare them.
If the other date Time variable that you want to compare is the current Date, then you don’t need to convert it String. You can use it as
You can perform the Comparison in the below way :
strSurveyTime is not the Current Date, then the above Expression will return as
Check the below as well :
May 17, 2023, 8:21am
The first time I run my process, I’ll create the dtNow variable. At the end of the process, I’ll assign a dtLastRun = dtNow so the next time I run the process, I can pick up the dtSurveyTime from the Excel and see if the survey was received after the dtLastRun, so it won’t just be looking at the date it’ll be comparing the time too. Hope that makes sense!
I thought it would be quite easy haha but now I’m finding it a struggle