Convert excel formula in numbers

Question

Level 1

4 points

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

Reply

Answer 1

Top-ranking reply

Recycleur

Level 4

1,663 points

Aug 30, 2023 2:48 PM in response to alfiejulian

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 { }.

Reply

Answer 2

SGIII

Level 8

37,562 points

Aug 30, 2023 5:10 PM in response to alfiejulian

Similar approach using a lookup table, but more "Numbers style" with clean separate tables without "Excelsy" blank columns in tables, etc.

Formula in J2, filled right K2, then down the columns:

=XLOOKUP(F2,Daycount::$A,Daycount::$C,"")

Formula in C2 of the lookup table, filled down the column:

=INT(DUR2DAYS((WEEKDAY(Month::A$1−B2,2)+EOMONTH(Month::A$1,0)−Month::A$1))/7)

The Dayname and Daynumber columns were filled in manually.

Sample Numbers spreadsheet here (Dropbox download) to get you started.

SG

Reply

Answer 3

Recycleur

Level 4

1,663 points

Aug 31, 2023 8:07 AM in response to SGIII

No, the problem is that he entered August 31st in the Month cell with the TODAY function. The formulas are correctly identifying one Thursday from August 31st to August 31st.

Paul, you should type Aug 2023 in the cell. This way Numbers will default to Aug 1st and the days of the whole month will be counted.

Seeing that you have date format YYYY-MM-DD I'm tempted to say that you may be using French Canadian formats for the document, so either you enter août 2023 or 23-8-1. Either way you may force it to display as YYYY-MM-DD in the Format pane. TODAY is a function that always returns the current day, so tomorrow Sept. 1st would return different results, and you don't want that for compiling lesson hours.

Reply

Answer 4

Recycleur

Level 4

1,663 points

Sep 1, 2023 3:19 AM in response to alfiejulian

Since you have an iPhone you probably have an iCloud account too. Go to iCloud.com on your Windows PC, log in with your Apple account and you'll be able to use an online version of Numbers to work on the spreadsheet.

Actually, the file Dropboxed by SG was pretty much complete, you could almost send it (or the DB link) directly to your teacher and let her try it as is. Through iCloud you can also share it with her during the development phase and you can both access it simultaneously.

Reply

Answer 5

SGIII

Level 8

37,562 points

Aug 31, 2023 8:21 AM in response to Recycleur

Ah, yes, the formula as written needs the first of the month. To always have beginning of the current month one could use something like:

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)

I think YYYY-MM-DD format works in most regions to display dates. I use it all the time with my region set to US.

I'd like to enter dates that way too, but haven't figured out a way to do so when the region is set to US.

SG

Reply

Answer 6

alfiejulian Author

Level 1

4 points

Aug 30, 2023 3:06 PM in response to Recycleur

Hello

I think I’m an idiot here. I started off using your first formula EOMONTH etc…am I doing it on another tab/sheet. I started with step 1 of your notes and mirrored the row and columns on a separate tab in the workbook and it gives me an error. So I couldn’t proceed with the rest of the instructions. Am I supposed to make a table first? Believe it or not, it took me 2 hours on you tube to get the original excel formula. I was so bummed out when it wouldn’t convert.

Reply

Answer 7

Recycleur

Level 4

1,663 points

Aug 30, 2023 3:41 PM in response to alfiejulian

The beautiful thing in Numbers is that you can have many tables on the same sheet. So Weekdays in month is just a small 9 rows x 3 columns table that you create on the same sheet than the other table, using the Insert / Table menu or the Table icon in the toolbar.

One other thing I wanted to add. After all is done you can select cell F5 in Table 1, and format it as Pop-up Menu. Enter the days of week in the list.

When you're done click on the arrow on the right of cell F5 and select None, then copy cell F5 and paste it in G5 and below.

Reply

Answer 8

stfflspl

Level 3

593 points

Aug 31, 2023 5:37 AM in response to SGIII

SG, I downloaded the Sample Numbers spreadsheet from Dropbox and entered the date TODAY (23-08-31).

This is the result of the calculations:

The numbers in the "Count in month" column are wrong.

I am wondering what is happening with the calculation of the formulas?

Paul.

Reply

Answer 9

Recycleur

Level 4

1,663 points

Aug 31, 2023 8:40 AM in response to stfflspl

Out of curiosity, how do handle the Extra and Excused values? Do you just respectively add and subtract them from the other day counts for calculating the amounts owed?

Not entering the first day of the month may have some advantages, like after Christmas if you only start lessons on January 5th for example. Teacher's mid-month vacations would be entered in the Excused column I guess.

Since you're a Numbers neophyte you may not know that you can duplicate an entire sheet with the menu on the sheet tab. This way you can keep past months' records each with their own Daycount table. Rename sheets by double-clicking on their name.

Reply

Answer 10

alfiejulian Author

Level 1

4 points

Aug 31, 2023 9:21 PM in response to Recycleur

let me clarify that I am working with excel at home on this. I do not have access to numbers except through my iPhone. Logging in to this website is being done through my iPhone which makes this difficult. My plan is to email the file to my teacher and she can see if it works in her numbers. I have tried to follow all the comments but can’t seem to get this to work one excel. I tried the Dropbox and was able to open it on my iPhone only.

what I think is the problem is that I need a step by step meaning 1. Create this box on cell xxx 1st. Step 2. Create a table in box 3 by doingxxxx. Basically assuming the columns and rows as above, what do I do and where do I do it. I was hoping to attach the file but can’t figure out how to from my iPhone.

The teacher just counts manually the number of mondays, tuesdays etc from the first day of the month. We don’t care about holidays etc. also please note that the sample boxes in orange are the days of the week that the students take lessons and she inputs that manually. My formula in J and K were reading whatever days she put in according to whatever the month was. The new student special line in green was a field that is calculating else where if there are new students getting a special rate so don’t touch that cell.

I hope this is making sense and appreciate any assistance on this project.

Reply

Answer 11

alfiejulian Author

Level 1

4 points

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

Hello,

Are you able to put the formula in the file attached so I don't have to recreate all the info? I think also then new student special boxes can't be touched. As that cell is doing something else in the formula. But feel free to add whatever u need to in another cell. I appreciate it. I did ask the teacher and she is using google sheets in her Mac. I would assume whatever u made formula wise will work on her Mac anyway. Numbers or google sheets. Since I don't use either, I have no idea what she is talking about. I tried to do what you did and made a dropbox account to attach the excel file. I hope it works.

[Edited by Moderator]

Reply

Answer 12

alfiejulian Author

Level 1

4 points

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

OK I just got an email from apple saying it edited my post because of an inappropriate link. I just copied the excel file and uploaded to the dropbox.com and copied the link to attach to this support. Is there another way to get the actual file uploaded?

Reply