[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Formula date / Time issue

Posted on 2011-10-11
12
Medium Priority
?
245 Views
Last Modified: 2012-05-12
Hi Experts,
I have the following formula that looks up start and finish times then looks up a hour figure based on those start and finish times. The problem I have is that the start and finish times are actually a date and time, so the formula below is looking up date and time...I need it to only look up the time.
Can someone please help. time format needs to be 'h:mm AM/PM'

=(IF(F5="","",(IF(H5=1,(INDEX('TimeChart - Table 1'!$F$9:$W$18,MATCH(F5,'TimeChart - Table 1'!$B$9:$B$18,0),MATCH(F6,'TimeChart - Table 1'!$F$4:$W$4,0))),(IF(H5=0.5,(INDEX('TimeChart - Table 1'!$F$33:$Q$42,MATCH(F5,'TimeChart - Table 1'!$B$33:$B$42,0),MATCH(F6,'TimeChart - Table 1'!$F$28:$Q$28,0))),(IF(H5=0,(INDEX('TimeChart - Table 1'!$F$58:$AD$79,MATCH(F5,'TimeChart - Table 1'!$B$58:$B$79,0),MATCH(F6,'TimeChart - Table 1'!$F$53:$AD$53,0))),"ERROR"))))))))

Thanks

Rob.
0
Comment
Question by:RobJanine
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 36954015
Can you post a sample file so that a working solution can conveniently be tried?
0
 

Author Comment

by:RobJanine
ID: 36954023
thanks:
Date-Issue.xlsx
0
 

Author Comment

by:RobJanine
ID: 36954030
the date / time comes from a formula in that cell looking up dates / times from a list depending on the employee id number.
Just so you know that I cant change the date/time cell.....

Thanks
Rob.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Expert Comment

by:VenuChakkoth
ID: 36954132
Hi Rob,

To strip off time from a date time field try this formula "= (A1-Int(A1))" where A1 is the field which has the time or date time value.

Please try this and let me know if it works...

Thank you
0
 
LVL 26

Accepted Solution

by:
redmondb earned 2000 total points
ID: 36954173
RobJanine,

Should the tables in "TimeChart - Table 1" really have different formats ("3:30 AM" v. "3.3")? Assuming that they can all be standardised to "h:mm AM/PM", please try the following...
=(IF(C5="","",(IF(E5=1,(INDEX('TimeChart - Table 1'!$C$5:$AD$24,MATCH(TIMEVALUE(TEXT(C5-INT(C5),"h:mm AM/PM")),'TimeChart - Table 1'!$B$5:$B$24,0),MATCH(TIMEVALUE(TEXT(C6-INT(C6),"h:mm AM/PM")),'TimeChart - Table 1'!$C$4:$AD$4,0))),(IF(E5=0.5,(INDEX('TimeChart - Table 1'!$C$29:$AD$48,MATCH(TIMEVALUE(TEXT(C5-INT(C5),"h:mm AM/PM")),'TimeChart - Table 1'!$B$29:$B$48,0),MATCH(TIMEVALUE(TEXT(C6-INT(C6),"h:mm AM/PM")),'TimeChart - Table 1'!$C$28:$AD$28,0))),(IF(E5=0,(INDEX('TimeChart - Table 1'!$C$54:$AD$79,MATCH(TIMEVALUE(TEXT(C5-INT(C5),"h:mm AM/PM")),'TimeChart - Table 1'!$B$54:$B$79,0),MATCH(TIMEVALUE(TEXT(C6-INT(C6),"h:mm AM/PM")),'TimeChart - Table 1'!$C$53:$AD$53,0))),"ERROR"))))))))

Regards,
Brian.
0
 

Author Comment

by:RobJanine
ID: 36954212
Sorry, I hadnt changed all the formats.
your formula works great. when format changed to h:mm AM/PM
Just one question, is it possible to change it to 3.30 PM? Instead of 3:30 PM.
The users are used to entering 3.30 pm in my old program.

Thanks

Rob
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36954335
Rob,

Not if they're keying directly into C5 etc. The problem is that that is not a date format recognised by Excel and so it's treated as a string.

Oh, you mentioned that C5 is the result of a formula - that could convert their entry into a date+time.

My "solution" is not a good thing to have in a production environment. What about a helper column to simplify?

Regards,
Brian.

0
 

Author Comment

by:RobJanine
ID: 36954370
Sorry Brian...what is a helper column.
The date/time formula pulls figures from a finger print reader. then on the time sheet the supervisor has the option to type striaght over the formula and type a time in. This workbook is a template.
Just means if they want to type a time in manually the it will have it be h:mm AM/PM.

Thanks

Rob.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36954400
Rob,

"Helper column" - an extra column added to simplify a formula. For example, in this case it might convert the date+time into just a time, so simplifying the "Match" formula. Could also easily handle "d.mm" format as well.

I'm afraid I'll be gone for a couple of hours. Anyone else, please feel free to dive in.

Regards,
Brian.
0
 

Author Closing Comment

by:RobJanine
ID: 36954436
Thanks for all your work Brian....I will look into it more

Thanks

Rob.
0
 
LVL 1

Expert Comment

by:VenuChakkoth
ID: 36954442
Hi Brian & Rob,

If the user keys in the time as 3.3 instead of 3:30 AM / PM then, you just need to modify Brian's formula to

=(IF(C7="","",(IF(E7=1,(INDEX('TimeChart - Table 1'!$C$5:$AD$24,MATCH(VALUE(TRIM(LEFT(TEXT(C7-INT(C7),"h.mm AM/PM"),5))),'TimeChart - Table 1'!$B$5:$B$24,0),MATCH(VALUE(TRIM(LEFT(TEXT(C8-INT(C8),"h.mm AM/PM"),5))),'TimeChart - Table 1'!$C$4:$AD$4,0))),(IF(E7=0.5,(INDEX('TimeChart - Table 1'!$C$29:$AD$48,MATCH(VALUE(TRIM(LEFT(TEXT(C7-INT(C7),"h.mm AM/PM"),5))),'TimeChart - Table 1'!$B$29:$B$48,0),MATCH(VALUE(TRIM(LEFT(TEXT(C8-INT(C8),"h.mm AM/PM"),5))),'TimeChart - Table 1'!$C$28:$AD$28,0))),(IF(E7=0,(INDEX('TimeChart - Table 1'!$C$54:$AD$79,MATCH(VALUE(TRIM(LEFT(TEXT(C7-INT(C7),"h.mm AM/PM"),5))),'TimeChart - Table 1'!$B$54:$B$79,0),MATCH(VALUE(TRIM(LEFT(TEXT(C8-INT(C8),"h.mm AM/PM"),5))),'TimeChart - Table 1'!$C$53:$AD$53,0))),"ERROR"))))))))

I had to use the trim(left()) function to adjust the time post 12:00pm since the users will be entering 1:00 pm as 1 only... Brian, not sure if theres a better way to achieve this...
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36954700
Rob, many thanks - although I'd strongly recommend a new question to clean up my solution! (It may work now, but I don't fancy the task facing the person having to maintain it in 6 months time - even you.)


Hi, VenuChakkoth. Nice try! Unfortunately Rob mentioned that his users enter the time as "3.30 pm" - which Excel is going to treat as a string.

Regards,
Brian.

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question