Solved

Posted on 2011-10-11

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.

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,(IN

Thanks

Rob.

12 Comments

thanks:

Date-Issue.xlsx

Date-Issue.xlsx

Just so you know that I cant change the date/time cell.....

Thanks

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

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,(IN

Regards,

Brian.

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

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.

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.

"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

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

Regards,

Brian.

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,(IN

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...

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.

Title | # Comments | Views | Activity |
---|---|---|---|

Posting V12 | 2 | 17 | |

Printer Setup Pop-up when Opening Excel File | 4 | 24 | |

concatenate 6k cells - automate creating the formula | 4 | 31 | |

.xltm file opens as .xlsx file | 3 | 24 |

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

Connect with top rated Experts

**16** Experts available now in Live!