Working out if a date field is formatted correctly

[Anonymous]

Hi all,

I have created a flow which runs once a month. It looks up a table in a spreadsheet, converts it to an HTML table and sends it in the body of an email to certain recipients. Everything works, except the date column. And I suspect it is because users can either enter a date, or just write free text (see screenshot below):

Below you can see how the entries look in my email body:

So I guess my question is. How do I turn the 5 digit number back into a date format? I assume somehow the date has changed and it now appears as a 5 digit number beginning with a 4?? I only want a date in that column if a date has been entered, otherwise I would like the free text to remain as free text.

I assume I would need to wrap the Leaving Date item here in some kind of if statement?

Thanks,

Freddie

[eliotcole]

I believe this is a UNIX date or some other kind, @Anonymous , lemme look it up, quickly. 🙂

The usual standard reply that you'll get from the robots here will be something about counting up from 1970 or somesuch ... I'm pretty sure there's a much simpler solution. ( but i've been wrong before XD )

[Anonymous]

Thanks Eliot. I think the number represents the number of days since the date: 1/1/1900

[eliotcole]

Right, and there's the 'ticks' function which can convert a date *into* that, I'm just trying to grep/remember the reverse of it. Sorry ... again, I could be way off here. But we can all agree it's a pain to draw up logic to work that out every time.

Out of interest, is there a reason why you can't move the datasource to SharePoint, then just export it to excel for the few people that still need an excel sheet? (that isn't a chastisement, just that quite often it's something people just haven't though of or done for a good reason)

[Anonymous]

No problem at all. Basically, I need to somehow tell the select part of my flow to:

1. Use the date (if a date has been entered in the spreadsheet)
2. If no date has been entered then the field be left blank OR use the words/letters that have been entered.

I thought exactly the same thing. Unfortunately the spreadsheet is massive and has loads of tables in it. So it would be very difficult to move them all over to SharePoint. I did suggest we did this because I don't like working with Excel in flow (compared to SharePoint).

[eliotcole · Accepted solution]

I'm with you on that mentality, there. Some folks just legacy plop data that should be in a database into Excel ... it's  a calculator, not a rolodex! 😅

The simplest solution is to create a hidden helper column in Excel that formats the date into an acceptable format elsewhere.

2021-07-19T15:55:12.6308941Z

yyyy-MM-ddThh:mm:ssZ

Then pick that column out.

Then create a separate helper column that simply checks for blank entries, and use that to create a boolean in Flow.

You could process the empty values, too, though. But it sounds like you'd be good enough to work out that logic.

I'll keep looking, though.

((( just editing in to say that I've **DEFINITELY** done this before, and worked out an efficient way of handling it that didn't involve extra actions ... the annoying thing is that it might be a formula, in which case it's not really helping the next person that comes to the flow 😞 ... )))

Further Edit:
OK, I haven't worked it out yet (I'm no maths wiz), but some of the stuff I did here will maybe help.

I would guess that you need to work out whatever the multiplication amount is to multiply the number by, and you'll  ... oh ... something something something maths. 😅

[eliotcole]

It's probably not worth overthinking this.

Use Ryan Maclean's (@RyanMaclean365 ?) method, here:
Converting Excel Date Time Serial Values With Power Automate – Ryan Maclean (ryanmaclean365.com)

Yep, just tested that and it works perfectly.

If you want to make life easy for yourself make a variable to store the excel number in, and replace "triggerBody()['text']" with the variable:

addseconds('1899-12-30',int(formatnumber(mul(float(triggerBody()['text']),86400),'0')))

[Anonymous]

Thanks for getting back to me Eliot. Would you be able to explain a little bit more where/how I do this? I tried using the expression: 

addseconds('1899-12-30',int(formatnumber(mul(float(item)?['Date Leaving']),86400),'0'))) in my select part of my flow and it tells me the expression is invalid. Is this the right place to be trying this expression?

I am trying to add it into the value field next to the Date Testing key field.

Thanks,

Freddie

[Anonymous]

Sorry. Just to add. I have tried using a variable as well. I get the error: Unable to process template language expressions in action 'Set_variable' inputs at line '1' and column '16940': 'The template language function 'float' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'. When I try and set the variable as below.

The expression I have used to set the value of the variable is: 

addSeconds(item()?['Date Leaving'],int(formatNumber(mul(float(item()?['Date Leaving']),'0'))))

Any help would be greatly appreciated!

Freddie

[eliotcole]

It won't work in the variable in that particular method, however, if you persist you should be able to add the expression into the field.

Sometimes it will say something is off ... then a few seconds later it accepts it. ¯\_(ツ)_/¯

Lemme test, though.

Edited to add: Remember, this isn't my solution, however I don't see why it shouldn't work ... although I'm not sure why the need for the formatNumber part.

Also: It looks like you have an erroneous reference to your date in that formula, BTW, plus you also have removed the multiplication.

[eliotcole]

Here's your correct formula for your select, @Anonymous :

addSeconds('1899-12-30', int(formatNumber(mul(float(item()?['Date Leaving']), 86400), '0')))

Have tested and seen positive results. 🙂

[Anonymous]

Hmmm. It still doesn't seem to work. I get the below error. Do you think it could be because in the spreadsheet there are items which aren't a date?

The execution of template action 'Select' failed: The evaluation of 'query' action 'where' expression '{
"Org Name": "@item()?['Organisation Name and/or Detail']",
"Whole Org": "@item()?['Whole Organisation?']",
"Date Leaving": "@item()?['Date Leaving']",
"Status": "@item()?['Status']",
"Payroll Area": "@item()?['Payroll Area']",
"Contact": "@item()?['Contact']",
"Tupe Out": "@item()?['Tupe Out']",
"Off-Boarding": "@item()?['Off-Boarding']",
"DFE Number": "@item()?['DFE Number']",
"Date Testing": "@addSeconds('1899-12-30', int(formatNumber(mul(float(item()?['Date Leaving']), 86400), '0')))"
}' failed: 'The template language function 'float' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'.

If we think that is the problem (I think it is). Is there a way to somehow wrap the expression in an If statement? So if the value in that cell is a date, it does the whole multiplication thing, otherwise it shows the text?

Thanks,

Freddie

[eliotcole]

Do what I did, @Anonymous .

Work backwards in the expression:

1. First add the float(), as that is the first entry.
2. Then add the mul(), as it's the second.
3. Then formatNumber().
4. Then int().
5. Then addSeconds().

Identify where the issue is in your expression. 🙂

It's usually a stray bracket or comma!

--------

Remember, there's always the easier option of making a helper column in the excel table (that you can hide from users there) which formats the date and time correctly.

------

Also, what happens when you bring the field over straight, with no formatting or expressions, does it accept it there?

As it might be that the excel sheet is formatting the field as text, rather than a number according to something done on that side.

[eliotcole]

Remember, there's always the easier option of making a helper column in the excel table (that you can hide from users there) which formats the date and time correctly.

------

Also, what happens when you bring the field over straight, with no formatting or expressions, does it accept it there?

As it might be that the excel sheet is formatting the field as text, rather than a number according to something done on that side.

[Anonymous]

I think the issue is coming from when a user doesn't enter a date into the spreadsheet and they enter some other text. Like 'TBC' for example. I have now tried to get around that using an If expression but I still can't get that to work.

if(startsWith(item()?['Date Leaving'], '4') and (length(item()?['Date Leaving'], '5')), addSeconds('1899-12-30', int(formatNumber(mul(float(item()?['Date Leaving']), 86400) '0', 'dd/MM/YYYY'))), (item()?['Date Leaving'])))

Something like the above would check that field to see if it starts with a '4' and has a length of 5 characters (the UNIX date or whatever it is called). If yes, then it carries out the add seconds expression and works out the date, if no, then it just returns whatever is in that field.

This still says the expression is invalid 😞

Freddie

[eliotcole]

You could:

1. Figure out how to handle the NULL or STRING variables (perhaps IF/THEN it to place 01/01/1900 as the date if there's an error).
2. Or, create an error thread to handle null/strings.

Figure out the Expression

On a NULL or STRING replace the value with the date above

Error Threading

With error threading ... it's basically two branches, and the second has a "Run after" value of only 'failed'. Meanind it will only run if the SELECT action fails.

If you haven't figured something out by tomorrow, hopefully I'll be around and will poke at your variant issues, here.

In the meantime, I highly advise that you just create a hidden 'helper column' in the excel sheet to do it there, and just pass perfectly formatted dates across. 🙂

I mean ... whilst also poking the people that insist on keeping it in excel and not SharePoint 😉.

[eliotcole]

@Anonymous how did you get on with errors?

I had a couple more thoughts on those. In excel you can add Data Validation to a field to ensure that they enter the correct information.

Another option is to move their *ENTIRE* input mechanism to a Microsoft Forms form. You could have it populate the data in the same spreadsheet, then update the table, accordingly. Or you could capture it in Power Automate, and then handle accordingly. There's a big chance the latter method could negate all this date wrangling, too!

[Anonymous]

Hey Eliot,

Sorry I meant to reply to you last week but completely forgot. I will mark your suggestion of a hidden field in Excel as the solution. Although I didn't use it in the end, I think it will work well. You'd probably need that maybe with a combo of what I ended up doing. I ended up using this expression: 

if(empty(item()?['Date Leaving']),null,if(and(startsWith(item()?['Date Leaving'], '4'),equals(length(item()?['Date Leaving']), 5)),addDays('1899-12-30',int(item()?['Date Leaving']),'dd-MM-yyyy'),item()?['Date Leaving']))

in the select part of my flow and it works perfectly 🙂 

Thanks very much for all your help and suggestions. I really appreciate it,

Freddie

[eliotcole]

Oh, excellent. Don't worry, I'm not fussed about getting points here ... just remembered you whilst handling something else, and wanted to check in! 🙂 Glad it's all good!