Hello everyone,
Can anyone please help me, how to read these 2 formulas and what they're basically doing?
1.Created Date
=IF(ISERROR(DATE(LEFT(B2,4),MID(B2,6,2),MID(B2,9,2))),DATE(MID(B2,7,4),MID(B2,4,2),LEFT(B2,2)),DATE(LEFT(B2,4),MID(B2,6,2),MID(B2,9,2)))
2.Time
=TIME(MID(F2,12,2),MID(F2,15,2),0)
Thanks in advance!
Welcome to the forum
1. A date is being constructed from the contents of B2. the first four characters in that cell are the year, characters at position 6 and 7 are the month and characters at position 9 and 10 are the day.
DATE(LEFT(B2,4),MID(B2,6,2),MID(B2,9,2)))
If that produces an error, it is using this formula: DATE(MID(B2,7,4),MID(B2,4,2),LEFT(B2,2))
In this case, the year is characters 7-10, the month is characters 4 and 5 and the day is the first two.
2. In the same vein as 1 above, this is creating a time.
TIME(MID(F2,12,2),MID(F2,15,2),0)
Characters 12 and 13 are the minutes and 15 and 16 are the seconds (or hours and minutes respectively, depending on the cell formatting).
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Hi Ali,
Thank you for being so fast and correct. This is really helpful.
I also have 2 more formulas where I need help.. and could you please inform me more about these 'Points'?
I want to have some but I don't have it clear how and where do I use them..
1.Stage Start
=IF(ISERROR(DATE(LEFT(F2,4),MID(F2,6,2),MID(F2,9,2))),DATE(MID(F2,7,4),MID(F2,4,2),LEFT(F2,2)),DATE(LEFT(F2,4),MID(F2,6,2),MID(F2,9,2)))
2.Stage End
=IF(I2=1,"",K3)
Thank you much!!
I have no idea how and where you will need to use these without sight of your data!
1. Same principle as number 1 in my first answer.
2. If cell I2 is equal to 1, return a blank, otherwise return the value of cell K3.
This one's pretty basic.
Well yes, I thought I know a little bit of excel but after I saw this what I'm working on, I doubt it!!
I have like 10-15 sheets, and 800 formulas (exaggerating!), all linked to another one and I can find myself lost even in these little simply ones.
Thank you for your help, I appreciate!
Firstly, please keep everything here in one place.
However, don't bombard us with formulae, many of which are very similar. You have a duty here to try to apply what we are telling you to interpret the other formulae. We need to see that you have tried - don't just present us with a huge list and expect a translation of each.
There is the Evaluate Formula feature on the Formulas ribbon in Excel that you can use to help you with this.
Sorry to disturb this much, but I thought I can get some help in here.
I was doing this Performance Measurement for a while.. I don't know how to use excel but I was doing this with a manual.. (export data, put everything in it's place and so). Now we have a new employee and I have to explain what she is supposed to do, but I cannot read these formula to her.
If this is not the right place here to ask, my apology!
Thank you!
You are not disturbing and yes, you can get help. But in return, we expect you to try to understand the help you are being given.
You will not be able to explain these formulae to your employee if you don't understand them. It's not simply a case of 'reading' them. In your other closed thread you presented us with a long list of formulae, many of which were very similar. Expecting someone here to 'translate' all of them for you is not going to get you any further forward than you are now.
What I would advise is this: have a look at the formulae you still need to understand. Run them through the Evaluate Formula feature and see what you can work out that way.
It's a bit like learning a language: you can run anything through Google translate, but it won't help you with the next sentence you have to decipher.
I hope this explains the approach I am taking with you on this.
Thank you much Ali
No worries. One step at a time.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks