spreadsheet and appsheet

[Amyosaka]

I am not sure if this is the place to ask.

I have this App made to collect dates into one cell on spreadsheet,

and then I want to count how many dates in one cell, 

I tried countif but it gives me back strange data, 

anyone know why this happens?

On the Datelist column I put this into [suggested values] :

if(
HOUR(TIMENOW()-"00:00:00")<9,LIST(TEXT(TODAY(),"yyyy/mm/dd"),TEXT(WORKDAY(TODAY(),1),"yyyy/mm/dd")),LIST(TEXT(TODAY()+1,"yyyy/mm/dd")))+LIST(
TEXT(WORKDAY(TODAY(),2), "yyyy/mm/dd"), etc.

The spreadsheet:E2:=countif(D2,"*"),and it gives me:1899/12/31, but I want it gives me back:6

[Kirk_Masden]

Hi!  I'm responding to this from Kumamoto, Japan.  I wonder if you are not in Japan too.
At any rate, it seems that your dates are separated by " , " (a space, a comma, and another space).  If you use SPLIT() you can count the number of dates in your cell.  I think the following should work:

COUNT(SPLIT([Datelist]," , "))

[Kirk_Masden]

By the way, are  you most interested in counting inside your app or in the spreadsheet.  The technique I suggested is for inside your app.  I think, however, that a similar technique would work in a spreadsheet.

[Amyosaka]

Hi @Kirk_Masden , indeed I am here in osaka Japan. Thank you very much for your advice.

 C column collects names, D column collects datelist, I want to count each person"s  total Dates at the end of each month,  and also count each person today"s date on every day base. 

I will try split to see how well I can do.  

[Kirk_Masden]

Great!  I suspect that SPLIT() will be part of what you need to do.

You may want to make a virtual column that eliminates any dates before the start of the current month.  You can use SPLIT for that too.  I would do it as follows:

1. Make the dates into a "list" using SPLIT() and add the beginning of the month to that list and one more date (any date) that is earlier than that one (to add dates to the list, you will need to use the LIST())
2. Reorder the dates using SORT()
3. Use CONCATENATE() to make the list into text again
4. SPLIT() the text using the beginning of the month, which you added earlier
5. Use INDEX([column name],2) to get everything after the beginning of the month
6. Use SPLIT() again to make the dates into a list
7. Use COUNT() to count the number of dates in the cell that came after the start of the month

These "steps" with take the form of a long, nested expression.  The early steps will be in the middle and succeeding steps will be put on the outside.

[Amyosaka]

Thank you, it will take me a lot time to degest your explaination. 

Btw, are you saying something like I can collect the datelist into multiple cells directly from the App without using formula[split] in the sheet? it is now all going into D2 which is undoutedly under expectation because I created D2 as Datelist 

[Kirk_Masden]

I was thinking about how to do what you want to do inside of AppSheet, not on the spreadsheet.  D2 is a cell in your spreadsheet, right?  And, I think you said you have many dates in that single cell.  So, I was thinking about how to parse the data in each cell to determine how many dates in that cell are equal to or later than the beginning of the current month. 

[Kirk_Masden]

Actually, though, I think it's possible to do the same sort of thing in a spreadsheet.  But, if you are using AppSheet, it might be better to do it in AppSheet.

[Kirk_Masden]

If you would like me to, and if you are looking for an expression that you can put in a virtual column that will count the number of dates that are from the past month, I can try to write the expression for you.  But first I'd like to confirm a few things.  First, would you like to do this in AppSheet or in your spreadsheet.  If you'd like to do it in AppSheet, the column name is [Datelist] right?  What kind of column is it -- text or list or something else?

[Amyosaka]

Thank you very much. This is my first time to use Appsheet so I want make it as simply as possible. 

It is a lunchbox order App for my colleagues, everyday before 9am someone will go to 3 different offices to count how many people want to have lunchbox. There are people who order lunch on a daily base and there are also people order on week or month base,  and they always change their orders too. 

So far because of your people:s help I finaly created something like below:

For counting, I count today:s order before 9am , at the end of each month I count each person:s orders for the whole month. 

For countings I use countif formula. 

I do not know if there are any other way to do what I want to do. 

[Kirk_Masden]

This is helping me understand.  I have two questions.

1. Would you prefer to find the totals you are looking for in your spreadsheet (using formulas on the spreadsheet side) or do you want to do this in the app?  It sounds like you are more familiar with spreadsheets so that may be easier for you -- particularly if you don't need to display the results of the calculations in the app.  If, however, you want the results of the calculations to show up right away inside the app, you will probably need to use expressions in virtual columns.  That brings me to my second question: 

2. Are you familiar with virtual columns?  Does your app have any columns that aren't in your spreadsheet?