I have created a microsoft flow which feeds email receipt date and time into an excel and I using it as a datasource for analysis. Emails are the batch job completion notification. Some jobs typically finish before midnight but once in a while job crosses midnight and the average come out wrong. i.e. on 09/02/2017 job finished at 23:00 while on 09/04/2017 job finished at 1:00. When averaging the time instead of getting 00:00, I get 12:00 which is not what I am looking for. One solution I have in mind is that I can potentially convert time to a number i.e. 09/02/2017 completion time 23.0 and if job completion time is after midnight I add that duration to 24 to get the correct average i.e. 09/04/2017 1:00 will become 24.0+1.0 = 25.0. Now when I take the average of 23.0 and 25.0 it will come out to be 24.0. But things will get tricky if job consistently finishes after midnight but sometime finishes before midnight, in that case I will get number like 24.5 etc which doesn't make sense to everybody. I can create specific logic for each job based on frequency of job finish before or after midnight, but I am wondering if there is a better solution out there where I can do this averaging without having to convert time into a numerical value on writing scenario specific rules as in the future if the frequency of the job finish timing changes, I will have to analyze and redo the logic.
