update values in a column everyweek by one

[waempix]

Hello . I don’t know if anyone could help me with this formula. Anyway let me explain. In E column I have column named preg.week . There are different values for each patient.I want excel update these numbers every week after i enter the value to E column. For example E3 column has value as 8 I want it be 9 next week and apply this formula for whole E column. I want to do this so i can follow which patient is closer to give a birth since it updates itself everyweek. Could you help me with this? Thank you.

[gak67]

Rather than add 1 to a week number, can you calculate the number of weeks from a set date? You must have an estimated pregnancy start date to get the number of weeks, so maybe a simple formula like:

Please Login or Register  to view this content.

[alansidman]

crossposted: http://www.mrexcel.com/forum/excel-q...yweek-one.html

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

[waempix]

crossposted: http://www.mrexcel.com/forum/excel-q...yweek-one.html

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

I apologize for my mistake. It is an urgent matter which i have to figure out . I didnt know this rule. Thank you for explaining. If It wasnt an important matter . I would read the rules about it. I have been searching google all day. I couldnt find a way to solve it . I am new to excel. Please help me. Thank you

[alansidman]

I want excel update these numbers every week after i enter the value to E column.

What are you entering that would then allow us to use that as a trigger and where specifically are you entering it?

[waempix]

Rather than add 1 to a week number, can you calculate the number of weeks from a set date? You must have an estimated pregnancy start date to get the number of weeks, so maybe a simple formula like:

Please Login or Register  to view this content.

thank you for your reply. I tried your formula but it converted it like 44543 something like that. I need to see it as week. There are a lot of patients to be recorded. For me to see which lady is close to give a birth is very important. I can ascend or descend to see it if it is only weeks . Maybe you can take a look at my excel file. if possible. Thank you very much

[alansidman]

Try this VBA solution:

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

[waempix]

What are you entering that would then allow us to use that as a trigger and where specifically are you entering it?

Hi. let me explain like this for example i had an appointment with a patient on 31-09-2015 and she is pregnant for 6 weeks already. So when i make her first entry to my excel file i will see her 6 weeks pregnant and after this i want this excel file to update itself next week on 8th of september. Then show patient info as 7 weeks and it keeps going like this till 40 .

[gak67]

thank you for your reply. I tried your formula but it converted it like 44543 something like that. I need to see it as week. There are a lot of patients to be recorded. For me to see which lady is close to give a birth is very important. I can ascend or descend to see it if it is only weeks . Maybe you can take a look at my excel file. if possible. Thank you very much

Try this (brackets included this time)

Please Login or Register  to view this content.

:

If that doesn't work, can you post an example file so we can see what data you are working with? Remove any confidential data before posting it.

[waempix]

Try this VBA solution:

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

I tried it but everytime i start the macro it adds 1.

[waempix]

Capture.JPG
https://drive.google.com/open?id=0B1...DFSY3ZJbWpIOE0 here is the link of the excel file

I add D column it is the first examination date of the patient and in the next column you can see the pregnancy week

thank you all for trying to help.

[waempix]

Try this (brackets included this time)

Please Login or Register  to view this content.

:

If that doesn't work, can you post an example file so we can see what data you are working with? Remove any confidential data before posting it.

i also tried this it gives me 6096 I sent the link of my excel file also a photo. It is a template i need to complete it to record patients as soon as possible. Thank you for your all effort.

[alansidman]

I tried it but everytime i start the macro it adds 1.

That is exactly how it was designed. You would run only once a week. Perhaps on Monday morning?

Alternatively, you try using the DateDiff function.

Look at this link

http://www.techonthenet.com/excel/formulas/datediff.php

[gak67]

See attached example. The first option (column E) is my formula which shows as whole weeks, eg it shows as 0 until more than 7 days has passed. The second option (column F) rounds it to the nearest week, eg. if it was 4 day it would show as 1 week, but if it was 3 days it would show as 0 weeks.

[alansidman]

Using the DateDif function. Look at the attached.

=DateDif(StartDate,EndDate,"D")/7

[waempix]

See attached example. The first option (column E) is my formula which shows as whole weeks, eg it shows as 0 until more than 7 days has passed. The second option (column F) rounds it to the nearest week, eg. if it was 4 day it would show as 1 week, but if it was 3 days it would show as 0 weeks.

Finally i got the solution.:D I can start using it tomorrow. I really appreciate your help. Thank you for spending your time for me. I will use your solution and also alansidman both works great.

[waempix]

Using the DateDif function. Look at the attached.

=DateDif(StartDate,EndDate,"D")/7

This solution is really good . I dont have to add extra column because of this great solution. Thanx a lot for spending your time. gak67also gave me an another solution. Both of you really amazing. I also appreciate for your quick response. Thank you again. you saved me.