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

x
Solved

# Formula date / Time issue

Posted on 2011-10-11
Medium Priority
245 Views
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.

=(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
Question by:RobJanine
• 5
• 4
• 2
• +1

LVL 43

Expert Comment

ID: 36954015
Can you post a sample file so that a working solution can conveniently be tried?
0

Author Comment

ID: 36954023
thanks:
Date-Issue.xlsx
0

Author Comment

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

LVL 1

Expert Comment

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

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

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

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

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

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

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

Thanks

Rob.
0

LVL 1

Expert Comment

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

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

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ā¦
###### Suggested Courses
Course of the Month20 days, 10 hours left to enroll