What is the best app formula to calculate num...

[Tim_Meske]

What is the best app formula to calculate number of days past since new row was added?

[Aleksi_Alkio]

How about NOW() - MAX(TableName[DateTime])

[Tim_Meske]

@Aleksi_Alkio

Thanks for the insight.

Looks like I’m closer… The spreadsheet displays decimal days, which is fine.

The app displays what appears to be hours:min:sec.

-neither the spreadsheet or the app update as the duration extends. How to get current days displayed in app? -how do I force the app to display days rather than time?

[Tim_Meske]

I’m new to the MAX function.

Looks like used to determine highest value in an array.

Just to be clear, I am attempting to display the duration of days for each individual row.

With this in mind, does the MAX function still apply?

Need to display the elapsed time between when the row was created and NOW.

Thanks for your time!

[Tim_Meske]

Ok making more progress with one more snag… =HOUR(NOW() - [Date])//24 + MINUTE(NOW()-[Date])/1440.00

Minimized your formula and the ‘time duration’ is changing as it should be BUT only in the slice view _Form of my app.

Not sure why the slice view _Detail ‘time duration’ is locked along with the slice view _Table.

Any suggestion on why the different view types don’t display the correct ‘time duration’?

Also, note that after sync the app the excel spreadsheet located in BOX is not updating either.

Thanks!

[Aleksi_Alkio]

Okay… I misunderstood your request slightly. I thought you want to compare between the last record of your table and now.

When you want to show it dynamically, you need to use it with the virtual column. If you want to calculate with date only, you could write it like… HOUR(TODAY() - [Date])/24

[Tim_Meske]

@Aleksi_Alkio

Sorry about the confusion.

The ‘time duration’ displayed in view _Form worked perfectly over night!!

Don’t think the formula needs to change.

Not sure what to do so that the ‘time duration’ is properly displayed in view _Detail and view _Table and also the source spreadsheet???

These still show the ‘time duration’ at something other than actual ‘time duration’

[Aleksi_Alkio]

I can check the app if you want. I need account ID number and app name.

[Tim_Meske]

@Aleksi_Alkio

Is it common to share this?

Do you work for appsheet?

[Aleksi_Alkio]

Nobody else can’t open the app with the ID and app name. And yes I work for Appsheet

[Tim_Meske]

Hi.

Is it ok to post this publicly?

[Aleksi_Alkio]

It doesn’t matter but you can send the information to support@appsheet.com. I can read it from there.

[Tim_Meske]

@Aleksi_Alkio

Thanks.

Our app isn’t much special.

Regarding security/privacy, are you saying only Appsheet can do anything with the account number and app name?

[Aleksi_Alkio]

HOUR(NOW() - MAX(TableName[DateTime]))/24.0+MINUTE(NOW() - MAX(TableName[DateTime]))/1440.0

[Aleksi_Alkio]

That’s correct

[Tim_Meske]

@Aleksi_Alkio

Thanks.

Where exactly can I find the ID and app name?

[Tim_Meske]

ID: 52b7e437-94bd-47ad-8f9e-e83fce1c1c10

App Name: HookRate08-596778

[Aleksi_Alkio]

The ID is the 596778

[Tim_Meske]

@Aleksi_Alkio

Need anything else from me? What is the long code that I thought was the ID?

[Tim_Meske]

Not sure why the URL included ‘HookRate08’.

I thought I named the app ‘Hook Rate Development’.

[Aleksi_Alkio]

The HookRate08 is a shorter version of the name you saved it for the first time. It was probably like Hook Rate 08. The long code is for the URL.

[Tim_Meske]

@Aleksi_Alkio

Ok thanks.

Let me know if I need other info. while looking into the app.

[Aleksi_Alkio]

The reason is simple. The calculation is happening in a normal column and it will update the value only, when you open and save the record. That’s why it won’t update the duration in your Excel file. When you want to show it dynamically in the app, you should use virtual column but please remember you can’t save that value into your Excel file.

[Tim_Meske]

@Aleksi_Alkio

Thanks!

Sounds like adding a virtual column will fix the app displaying the current duration but the only option to update duration in the source spreadsheet is to drill down into ea customer and manually save it? — We will need the current/accurate duration to be displayed in ea slice view _Table.

This way we know duration without drilling down into the customer.

Will a single virtual column resolve this requirement?

If so, any special virtual column setup requirements I should know about?

I ask because I’m new to Appsheet and virtual columns.

— Thanks again!!

[Tim_Meske]

@Aleksi_Alkio

Wow!

Looks like it will work.

What ‘TYPE’ setting…Duration, DateTime, Decimal? Having some conflict

[Aleksi_Alkio]

Just add that existing formula into one virtual column and check from the slice it’s there as well.

[Tim_Meske]

@Aleksi_Alkio

Time duration is now dynamically displayed perfectly!!!

Thanks.

Now that it is dynamic via slices what is best suggestion to keep the spreadsheet current with the duration?

I’m assuming add the spreadsheet column to the slice and check hidden?

This way I have two durations; one to keep the spreadsheet current and one for the user.

Seems like there must be a smarter way to do this.

Thanks

[Tim_Meske]

FYI.

Appsheet tells me type of the app formula ‘decimal’ does not match the column type ‘duration’.

I tried changing from duration to decimal but no luck.

[Aleksi_Alkio]

It should be decimal.

[Tim_Meske]

No more errors after I tried decimal type again!

Will this app formula keep the column current as the days go by?

Will it need to be forced refresh, etc?

It calculated 0.1 days immediately.

Im assuming this is a rounded value??

[Aleksi_Alkio]

If it’s in a normal column, it will calculate the value only, when you open/save that record. If you add this formula into the virtual column, it will calculate new value every time you sync the app. Yes it’s rounded. You have probably chosed only one decimal?

[Tim_Meske]

@Aleksi_Alkio

Thanks again.

The time is stuck at 0.01.

I’m wondering if my start date column is set up properly.

Is there a format it should be?

The spread sheet resides in BOX.

It consist of one spreadsheet table and a few virtual slice views.

Trying to get the days to appear in the slice views.

[Tim_Meske]

Added additional row.

The day counter produced an initial value of 0.19.

Previous row was 0.01.

Must have a format issue?

Since the automatic date and time stamp created by appsheet is the only variable in your formula I’m thinking there is a format issue with the time and date stamp???

Does this seem likely???