I am trying to create an excel file where i put in the name of the person, location and there shift time (Start and End columns). I want to convert this shift time into EST or EDT as per the location.
- The person can be located anywhere in the world.
- The shift timing can vary from person to person.
The formula i have used does convert the time but where i get stuck is when the time is for the day before. The system shows an error.
Capture.PNG
Any help would be appreciated.
You have posted in the VBA section - shall I move the thread for you?
There are instructions at the top of the page explaining how to attach your sample workbook.
A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.
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.
Just to add on the formula i am using is as follows.
=IF($B$1="Yes",D4-VLOOKUP($C4,Sheet2!$B:$D,2,0),D4-VLOOKUP($C4,Sheet2!$B:$D,3,0))
The Sheet 2 has the table as follows:
Attachment 772906
So shall I move the thread? Please provide a sample workbook. Thanks.
Yes pls move it to the correct board
OK - will do. Waiting for you to attach a workbook.
I have attached a sample file
Try the following:
For column F:Formula:
Please Login or Register to view this content.
For column G:Formula:Please Login or Register to view this content.
Let us know if you have any questions.
Last edited by JeteMc; 03-22-2022 at 11:10 AM. Reason: Added file
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Re: Formula for time difference
This worked great for me. Thank you for the help.Originally Posted by JeteMc
Try the following:
For column F:Formula:Please Login or Register to view this content.
For column G:Formula:Please Login or Register to view this content.
Let us know if you have any questions.
Could you provide me with the explanation of what it is doing? I am not able to put my finger on the logic here.
Last edited by amitesh9; 03-23-2022 at 09:27 AM.
The formula in column F takes the corresponding time value in column D and adds 1 if the VLOOKUP($C4,Sheet2!$B:$D,2,0)>D4 portion returns True (value found on Sheet2 is greater than the value in column D).
If the VLOOKUP($C4,Sheet2!$B:$D,2,0)>D4 portion returns false then zero is added.
The formula then subtracts the VLOOKUP($C4,Sheet2!$B:$D,2,0) value to give the final time value.
A good way to see this at work would be to select cell F7, which previously returned an error, and utilize the Evaluate Formula feature on the Formulas tab.
Let us know if you have any questions.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks