Date in a formula

Hi BOO,

Several questions regarding your table and formula.

• I do not see columns with the header labels "Date" and "Pay". Where are these columns located?
• Where is the formula shown in the screen shot located? My assumption is cell D4 of the table shown in the screen shot.
• What result does the formula return? If my assumption above is correct, If it shows a red error triangle, it would be useful to know the contents of the error message flagged by that triangle. (Click the triangle once to show the message, then select and copy the message and post it in your reply.
• Do the cells showing $0.00 contain a similar formula?

Regarding 'easily populating' all of the cells, the answer is probably 'Yes, but…"

The details of the 'but…' part depend upon the answers to the questions above.

"[M]y goal here is to have a daily total based on a date in another spreadsheet for a whole year."

In Numbers, the term "spreadsheet" is short for "spreadsheet document," and refers to the whole document contained in a single Numbers file. Each document contains one or more Sheets, and each Sheet may contain one or more Tables.

If your goal can be restated as "[M]y goal here is to have a daily total based on a date in another Table (in the same spreadsheet document) for a whole year," then your goal is likely achievable.

If you do mean 'in another spreadsheet document)' it is not achievable with a formula, as Numbers formulas in one document cannot reference cells in a different document.

More to come following your reply to the question above.

Regards,

Barry

@Casuarina

Totally off topic, actually.

There's a name for this behaviour: "Thread hijacking"

Please repost as a NEW question in either the Numbers for Mac or the Pages for Mac community.

Regards,

Barry

PS:   Short answer is 'You can't'

but you could pate an image of the table,

B.

What is the character in the position shown by the number sign (#) in his copy of your formula?

SUMIF(Date,"="&B$1+ROW(#)-2,Pay)

There should be nothing in that location.

Regards,

Barry

PS: For diagnostic purposes, please uncheck the "Use header names as labels" box in Numbers Preferences > General.

I believe Barry means that if you are searching for a date formatted as Dec 28, 2020, the dates you are searching should be in the same Mon DD, YYYY format.

A couple of other ideas to check:

Ensure the numbers in your "Pay" column are formatted as currency, not text. I'm not sure I'd trust "automatic" either. Text will not SUM, even if that text looks like numbers. It will add/subtract, etc. with the +,-, etc operators but will not be included in the SUM functions.

Ensure all the dates in all the tables have the time 12:00:00AM. That is the default if you enter a date with no time but it is something to check anyway. If there is a time other than 12:00:00AM, that date will not match.

Ensure all the dates in all the tables are formatted as dates, not as text. I think Numbers converts them internally but why give one more reason for it to not work.

"When you say express both sets of dates are you saying from Dec 28, 2020 - Jan 3, 2021?"

No. I'm referring to the set of dates in column C of the "Log" table and the set of Monday dates in row 1 of the Summary table.

See Badunit's description in the first paragraph of the post above.

Badunit's other points are well-taken as well.

Regards,

Barry

A simple question, slightly off topic, but it is about Numbers. How can I copy a table from Numbers into a Pages document and have it viewed at a portrait not landscape aspect?

Many thanks.

The way you have it arranged makes it a little complicated. You need to determine the date from the text you have in row 1. This means parsing it to obtain to the starting date. The dates in the column are then calculated by adding the row number - 2 to the starting date.

The formula for the highlighted cell (D4) would be

=SUMIF(DATE, DATEVALUE(REGEX.EXTRACT(D$1,".+?(?=-)"))+ROW()−2, PAY)

If you modify your table and put only the date of Monday (1/11/21 versus 11/11/21 - 1/17/21), it could be simplified to

=SUMIF(DATE, DATEVALUE(D$1)+ROW()−2, PAY)

or

=SUMIF(DATE, D$1+ROW()−2, PAY)

A few words of caution:

1. It is probably best if you compare dates to dates or text to text, not a mix. The formulas above create date&time values. I do not know if the cells in your "Date" column are text or actual date & time values.
2. With date & time values, 1/13/2021 6:00PM is not the same at 1/13/2021 12:00AM. If you enter a date with no time, the default time is 12:00AM so this probably is not a problem but it is something to be aware of.
3. If you get a date wrong in row 1, you may not catch your error. You might consider adding another row that checks that the starting date you typed is actually a Monday. Not necessary but might be useful.

Much clearer.

Badunit's post contains most of what I was expectin to say after your clarification, so you'll probably find the rest of this post a bit repetitive.

Rename the Table 1 shown in you most recent post as "Log" (or something similar), using a name that is not repeated on any other table in the document. A short and distinct name keeps formulas shorter and easier to read.

For the example, I'll name the table shown in your first post "Summary".

For the most part, I'll show data in only thise cells involved in the calculations.

Your Log table and Summary table will look like this:

The formula below the Summary table is entered in Cell B2, then filled down to B7 and right for as many columns as yo have weeks.

Here is the same formula in text form, which can be copied and pasted into the formula editor for Summary::B2, then filled to the rest of the cells in the table.

Summary contains one more formula, entered into C1 and filled right for as many columns as needed:

C1: B1+7

Note that all dates shown in the table are Date and Time values with only the Date part entered and only the Date part displayed. All dates used should be displayed using the same format.

Dates in each column of Row 1 of Summary, including the date in B1, are the Monday date at the beginning of that week.

The formula in the rest of the cells in row 1 adds seven days to the date in the column to it's left.

Regards,

Barry