Hello,
Quick question, hopefully this doesn't require VBA.
In my spreadsheet attached, look at cell K5 (highlighted in yellow)
Instead of entering the month, I'd like to be able to enter P1, P2 or whatever period up to P12 instead of the date BUT the calculations would still happen as if it was a date in K5. (even better if I can put it in a dropdown list)
Mar-2024 represents P1
Apr-2024 represents P2
May-2024 represents P3 and so on
.
.
Feb-2025 represents P12
Thank you for your help.
Pat
The way to do this with the least impact is to put your selection in a different cell, then use K5 to translate the selection to date. Then none of your other formulas would have to change. See attached.
Jeff
| | |·| |·| |·| |·| | |:| | |·| |·|
Read the rules
Use code tags to [code]enclose your code![/code]
Thank you very much. I'd like to better understand what you did. It works perfectly. Can you please explain what you did?
In K4 I entered a dropdown list with P1,P2,P3...P12. You mentioned using a dropdown list, so I assume you know how those work but I can give more detail if you need it.
Then in K5 I replace the date with this formula:
Formula:
=EDATE(DATE(2024,3,1),VALUE(MID($K$4,2,2))-1)
MID looks at the value in K4 starting with the second character. This has the effect of removing the "P" at the beginning. Then VALUE changes it from a string to a number. That represents the month number. The DATE gives the start of the first month, March 1, 2024. EDATE adds months to that. So we take the number after P, subtract 1, and add that many months to the starting month. This gives you the date in K5.
Since you had a date in K5 before, no other formulas needed to change, just the method for getting the date.
Thank you very much for the explanation. Helps me get better. Cheers!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks