Convert excel formula in numbers

I am helping my piano teacher by building a spreadsheet that calculates the amount owed by student by the number of specific days in a month. My formula takes whatever date you put in a box and spits out the number of Sundays, Monday’s etc. That is the formula not converting to numbers. I use excel and she uses numbers that’s why I need to convert.

When I put the date in yellow column E, then whatever the days of the week are that the student gets charged for, it returns the number of those days in column J & K. Then column L adds the number of days calculated times the student rate. The formula that is not converting is the one in column J & K. The formula is attached. I would attach the actual excel file but don’t see that option.

Posted on Aug 30, 2023 1:23 PM

Top-ranking reply

Recycleur

Level 4

1,663 points

Posted on Aug 30, 2023 2:48 PM

I'd suggest creating a cheater table that you can cut (not copy) and paste on another sheet afterwards if you don't want to see it on that sheet. (it's just an option)

Formulas:

In C1, getting the date from the main table and making sure it is the first day of the month.

=EOMONTH(Table 1::E2,−1)+1

In C2, number of days in the month.

=DUR2DAYS(EOMONTH(C1,0)−C1)+1

In B3, first weekday of the month. Copy down the formula to the the 7th day.

=DAYNAME(C$1+A3)

In C3, count of that day in the month. Copy down the formula to the the 7th day.

=QUOTIENT(C$2,7)+IF(MOD(C$2,7)>$A3,1,0)

Back to the main table

In J5, just picking up the count from the small table. Copy to K5 after, and down how many rows you need.

=XLOOKUP(F5,Weekdays in month::$B$3:$B$9,Weekdays in month::$C$3:$C$9,0)

By the way, the Excel formula doesn't work in Numbers because it doesn't have the concept of array formulas with { }.

View in context

16 replies

alfiejulian Author

Level 1

4 points

Sep 17, 2023 3:23 PM in response to SGIII

I added a separate response and file in the dropbox for you to look at if you can.

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

Convert excel formula in numbers