MASOODAHMAD
Board Regular
- Joined
- Mar 28, 2012
- Messages
- 105
- Platform
- MacOS
Hello Everyone,
Hope you’re well.
The only Excel formulas, I always struggle with is the Date and Time formulas. I create Job Entry Reports and Dashboards for different departments in my company. Sometimes I also have to cleanup the Excel data to create a dashboard out of it. The major problem I see is with the Date column. Some people write: DD/MM/YYYY, some write MM/DD/YYYY, etc in the same column. So it is hard to figure out which is the correct date. Not only this, they sometimes copy & Paste the date from somewhere, which looks like a date but is not a date.
The first thing I do is, use the ‘ISDATE’ function to verify that it is a date or not.Then I struggle finding out whether the date entered is a DD/MM/YYYY or MM/DD/YYYY. I do all this manually, which eats up a lot of time. So is there any quickest and effective way of doing this.
In the attached Google sheet, I have tried to showcase the issues I faced with the Date and Time formulas.
In the ‘Date_Issues’ sheet, the data received form the Studio is in column B. The date has some prefixes. So, first thing I do is remove the prefixes from the dates (column D). Then I used the "ISDATE" function to see if the date is really a date or not. So, for all the FALSE conditions, I have to fix the dates manually.
Is there any quicker method to achieve this, considering the dates to be in MM/DD/YYYY format.
In the 'Time_Issues', sheet, I need to find the time difference, but I’m not getting the right results, if,
a) the date changes to the next date
b) the date changes to the next date and the out-time is higher than the in-time (more than 24 hours). See Red highlighted.
In regards to the TIME formula, let’s say, I want to find the difference between the two times, I can do it, NewTime - OldTime (column H). But the problem occurs when the day changes and so on.
I tried to expand the formula, but it still fails at many places (column F).
Can anyone please help me resolve this issue.
Have a great day.
Masood
Hope you’re well.
The only Excel formulas, I always struggle with is the Date and Time formulas. I create Job Entry Reports and Dashboards for different departments in my company. Sometimes I also have to cleanup the Excel data to create a dashboard out of it. The major problem I see is with the Date column. Some people write: DD/MM/YYYY, some write MM/DD/YYYY, etc in the same column. So it is hard to figure out which is the correct date. Not only this, they sometimes copy & Paste the date from somewhere, which looks like a date but is not a date.
The first thing I do is, use the ‘ISDATE’ function to verify that it is a date or not.Then I struggle finding out whether the date entered is a DD/MM/YYYY or MM/DD/YYYY. I do all this manually, which eats up a lot of time. So is there any quickest and effective way of doing this.
In the attached Google sheet, I have tried to showcase the issues I faced with the Date and Time formulas.
In the ‘Date_Issues’ sheet, the data received form the Studio is in column B. The date has some prefixes. So, first thing I do is remove the prefixes from the dates (column D). Then I used the "ISDATE" function to see if the date is really a date or not. So, for all the FALSE conditions, I have to fix the dates manually.
Is there any quicker method to achieve this, considering the dates to be in MM/DD/YYYY format.
In the 'Time_Issues', sheet, I need to find the time difference, but I’m not getting the right results, if,
a) the date changes to the next date
b) the date changes to the next date and the out-time is higher than the in-time (more than 24 hours). See Red highlighted.
In regards to the TIME formula, let’s say, I want to find the difference between the two times, I can do it, NewTime - OldTime (column H). But the problem occurs when the day changes and so on.
I tried to expand the formula, but it still fails at many places (column F).
Can anyone please help me resolve this issue.
Have a great day.
Masood
