Excel formula help please

[RiaX]

Something like this?
View attachment 1828813

The technical stuff:
Make sure that the "date" fields are formatted as dates and not general or numbers (I used a custom format dd-mmm-yy for the date shown here, but you can change it to whatever works for you)

To work out the dates I simply used =$A$3+(B2*7) and then replicated the formula to the right. All it does is add the total of the amount of weeks multiplied by 7 to the first date specified in cell A3. This way makes it easier if you want to add more weeks to the table or change the week count.

View attachment 1828816

I cant seem to get it right. Im getting a #VALUE!. Please see the image below to see what I did



I am very bad with excel so please try and explain as simply as possible

EDIT

I got it to work by using =date(2025;06;19) but now how to I have this value in all the cells on the date column so staff can just punch in the prescription date and it generates the repeat dates ? because they wont be able to do =date(2025;06;20) for example and its tedious to enter it like that all the time and how do I switch the function to be dd/mm/yyyy and not yyyy/mm/dd. Its displaying d/mm/yyyy but it has to be entered yyyy/mm/dd

 

[TribbleZA]

Now when I do that for example =A1 + 28 the cell just goes #NAME!!! Or something like that.

I'm using 28 days and 56 days for 4 weeks and 8 weeks to avoid weekends. I'm trying to schedule chronic repeats

Then your date possibly isn't formatted correctly. Is it on the left in the cell or the right?

Look at the date in the bottom corner of your screen (in windows) - you need to type dates in like that. It is more forgiving if the machine is set to dd/mm/yyyy but if you have it as yyyy/mm/dd then you have to type your dates in like that. You can then format the spreadsheet to show the date as you want it - but it has to be typed in correctly.

*Edit: Just seen your question
Go to regional settings. Go to additional settings and you can change it in there.



Select Additional settings

 

[Sagax]

Why the $ sign ? I used =A1 + 28 and it didn't work

The $ is an "anchor", meaning, when you replicate the formula to the cells on the right that particular cell reference will stay the same while the rest adjust according to position.

For example:
The formula in the first column is =$A$3+(B2*7)
The formula in the second column will change to =$A$3+(C2*7)
The formula in the third column will change to =$A$3+(D2*7)
The formula in the forth column will change to =$A$3+(E2*7)

So the formulas keep on referencing cell A3, while adjusting to the same column in the row right above them.

This is however based on the way I constructed my demo based on the original information that was provided, but later info suggests that the regional formatting as mentioned by @TribbleZA might be the cause of the problem that you are experiencing.

You can remove the DATE function from the formulas after the regional settings have been changed to the South African date format and it should display correctly.

 

[maumau]

The $ is an "anchor", meaning, when you replicate the formula to the cells on the right that particular cell reference will stay the same while the rest adjust according to position.

For example:
The formula in the first column is =$A$3+(B2*7)
The formula in the second column will change to =$A$3+(C2*7)
The formula in the third column will change to =$A$3+(D2*7)
The formula in the forth column will change to =$A$3+(E2*7)

So the formulas keep on referencing cell A3, while adjusting to the same column in the row right above them.

This is however based on the way I constructed my demo based on the original information that was provided, but later info suggests that the regional formatting as mentioned by @TribbleZA might be the cause of the problem that you are experiencing.

You can remove the DATE function from the formulas after the regional settings have been changed to the South African date format and it should display correctly.

@TribbleZA teaches Excel so she is gooooood

 

[rh1]

If you really want to amped it up, you can micrsoft visual basic to create outlook meeting invites for the dates.

 

[RiaX]

Thanks guys it worked. Thank you so much

 

[RiaX]

Ok guys qI have more excel troubles now. I have no idea how it happened but like 600 rows minimized to have basically disappeared. So it goes from row 199 then the next row is 800. If I double click the line then row 799 pops up.

How can I expand all the rows easily without double clicking the line 1000 times. I've tried adjusting the row height it didn't work.

Thanks

 

[PaulKempTheSecond]

Ok guys qI have more excel troubles now. I have no idea how it happened but like 600 rows minimized to have basically disappeared. So it goes from row 199 then the next row is 800. If I double click the line then row 799 pops up.

How can I expand all the rows easily without double clicking the line 1000 times. I've tried adjusting the row height it didn't work.

Thanks

Highlight from line 199 to 800, right click, unhide.

 

[TribbleZA]

Highlight from line 199 to 800, right click, unhide.

That should work.

You can also highlight all the rows and use this

It is on the Home ribbon. If the rows have text in - they will show if you use autofit row height. If they are blank they won't.
Unhide is also available on the same menu.

Edit: Ah just read you had adjusted the height and it didn't work. So the lines are blank?

 

[RiaX]

Thanks guys this worked

 

[RiaX]

That should work.

You can also highlight all the rows and use this
View attachment 1851918
It is on the Home ribbon. If the rows have text in - they will show if you use autofit row height. If they are blank they won't.
Unhide is also available on the same menu.

Edit: Ah just read you had adjusted the height and it didn't work. So the lines are blank?

Ok so now the problem is I have to unhide after I filter the patients i want then when i go to select all it hides a set of rows like number 250 to 800 then after 800 its normal. I don't know what happened for it to do this.

Yes I have blank columns but reach row has a number and a formula.

I would upload a picture but it's patient data so I can't.

 

[TribbleZA]

Ok so now the problem is I have to unhide after I filter the patients i want then when i go to select all it hides a set of rows like number 250 to 800 then after 800 its normal. I don't know what happened for it to do this.

Yes I have blank columns but reach row has a number and a formula.

I would upload a picture but it's patient data so I can't.

See if this helps.
On the Data ribbon you can find the clear filters button - it will remove all filters.

Perhaps there is a filter somewhere else that is active.

See if that works.