| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 150503 | 2022-02-26 04:05:00 | Excel expert wanted | Tony (4941) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 1484606 | 2022-02-26 04:05:00 | Just for fun I'm creating a spreadsheet that contains the birthdate of all my nieces, nephews etc, and I want to display their exact ages whenever I open the workbook. This formula works: =DATEDIF(D4,TODAY(),"y")&" years, "& DATEDIF(D4,TODAY(),"ym")&" months, " & DATEDIF(D4,TODAY(),"md")&" days" Cell D4 contains dd/mm/yyyy date of birth. It displays "11 years, 4 months, 16 days" for a birthdate of 10/10/2010. So far so hoopy, but I have to tediously enter this formula with a different cell reference for each person (about 20 people). There are two things I would like to do: I would like to be able to generalise so the formula is only entered once I would like it to display "month" or "day" rather than the plural when the value is 1. For the latter, this works: =IF(DATEDIF(D2,TODAY(),"ym")=1,"month","months") but once again it makes the whole thing really unwieldy. Any ideas as to how I could simplify/generalise it? The irony is I could do it (I think) in Access, but I want to be able to distribute the result round the family, and my Excel expertise is very sketchy. |
Tony (4941) | ||
| 1484607 | 2022-02-26 09:30:00 | I've done a lot of formulas in spreadsheets but it's been a few years and I'm very rusty. If you use a fill down on a formula doesn't it just increment the cell reference automatically? So as long as the cell the formula is in and the one it references are both in a column with the same spacing then just highlight the cells with the formula on top and ctrl - D and it should all happen automatically. I know I had to add a $ to a cell reference to stop this happening like $D4 to always reference D4 instead of changing it as you fill. I always had to work on formulas in small chunks and get it working before combining them into the large unwieldy type, it can just get too complicated to debug otherwise. When I was feeling less motivated I would sometimes just make a lot of simple formulae in separate cells and then hide them and reference then from the main cell I was using. For example you could make one in a hidden cell that just works out whether it's one month or more like you suggest and have it just return an s or a space then have your main formula just add that cell on to the end of your result with a +"D5" or similar. Kinda clunky but easier to work on. |
dugimodo (138) | ||
| 1484608 | 2022-02-26 19:09:00 | Google? Templates: trumpexcel.com |
piroska (17583) | ||
| 1484609 | 2022-02-26 22:00:00 | Thanks. I know about fill down and I used it in my spreadsheet. What I was thinking about more was being able to have the formula in one place and just reference it somehow from everywhere. My programming history kicking in I guess that makes me want to avoid repeating code wherever possible. :) Like you I tend to work out the elements then string them together. |
Tony (4941) | ||
| 1484610 | 2022-02-26 22:02:00 | Google? Templates: trumpexcel.com I got the original formula from Google, but thanks for the suggestion. I'm not sure I would trust anything from a site called TRUMPexcel. :) |
Tony (4941) | ||
| 1484611 | 2022-02-27 08:55:00 | Some $ dollar signs to be entered there. | decibel (11645) | ||
| 1484612 | 2022-02-27 18:52:00 | Some $ dollar signs to be entered there. Don't think that is going to help. |
Tony (4941) | ||
| 1484613 | 2022-02-27 20:37:00 | From my distant memory is is something like: linking the value of the new cell(a) = the value of the other cell containing the formula (b). I think. | PeterE (6851) | ||
| 1484614 | 2022-02-27 20:53:00 | What I am currently trying to do is create a custom function in Excel VBA that will do the job. So I will just enter =BIRTHDAY(d2) in the worksheet cells and get the response. At the moment I am getting a #NAME? error that I don't quite understand. | Tony (4941) | ||
| 1484615 | 2022-02-27 21:59:00 | I hate Excel VBA! I have created a test function called birthday: Function BIRTHDAY() Dim answer As String Dim Startdate, Enddate As Date Startdate = "3/3/1970" Enddate = "5/6/1970" answer = Application.WorksheetFunction.Days360(Startdate, Enddate) Debug.Print answer BIRTHDAY = answer End Function If I run that in the VBA environment it works and displays 92 in the immediate window. In the spreadsheet I enter =BIRTHDAY() in a cell. This gives me a #NAME? error, and I don't know why. |
Tony (4941) | ||
| 1 2 | |||||