kelvin_9

Active Member
Joined
Mar 6, 2015
Messages
444
Office Version
  1. 2019
HI ALL,


I'm planning to use some formulas to return my employee's schedule which looks clean and more simply from a complicated database.
Here are what i have in the original schedule @ sheet_schedule for my 3x employee and a formula to return a concise layout @ sheet_result

90b476804b

88bdea43d7


formula in cell B51.....etc
Code:
=IF(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0))="","",TEXT(MIN(IFERROR(TIMEVALUE(LEFT(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0)):INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A52,SCHEDULE!$A:$A,0)-1),5)),1)),"hh:mm")&" - "&TEXT(MAX(IFERROR(TIMEVALUE(MID(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0)):INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A52,SCHEDULE!$A:$A,0)-1),7,5)),0)),"hh:mm"))

my first question is how can i amend with my above formula to return "AH09 Day 1.0/Vacation Leave Day 1.0" for Peter instead of "00:00 - 00:00" in cell D53/F53/H53?

thanks ALL for the great help:biggrin:
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
HI ALL,


I'm planning to use some formulas to return my employee's schedule which looks clean and more simply from a complicated database.
Here are what i have in the original schedule @ sheet_schedule for my 3x employee and a formula to return a concise layout @ sheet_result

90b476804b

88bdea43d7


formula in cell B51.....etc
Code:
=IF(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0))="","",TEXT(MIN(IFERROR(TIMEVALUE(LEFT(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0)):INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A52,SCHEDULE!$A:$A,0)-1),5)),1)),"hh:mm")&" - "&TEXT(MAX(IFERROR(TIMEVALUE(MID(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0)):INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A52,SCHEDULE!$A:$A,0)-1),7,5)),0)),"hh:mm"))

my first question is how can i amend with my above formula to return "AH09 Day 1.0/Vacation Leave Day 1.0" for Peter instead of "00:00 - 00:00" in cell D53/F53/H53?

thanks ALL for the great help:biggrin:

any help
 
Upvote 0
You might try something like this

Code:
=[COLOR=#333333]IF(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0))="","",[/COLOR][COLOR=#ff0000]if TEXT(MIN(IFERROR(TIMEVALUE(LEFT(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0)):INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A52,SCHEDULE!$A:$A,0)-1),5)),1)),"hh:mm")="00:00",INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0))[/COLOR][COLOR=#333333],[/COLOR][COLOR=#333333]TEXT(MIN(IFERROR(TIMEVALUE(LEFT(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0)):INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A52,SCHEDULE!$A:$A,0)-1),5)),1)),"hh:mm")&" - "&TEXT(MAX(IFERROR(TIMEVALUE(MID(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0)):INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A52,SCHEDULE!$A:$A,0)-1),7,5)),0)),"hh:mm")))[/COLOR]
 
Upvote 0
Sorry, I was missing a parentheses in the above reply. Please use this instead

Code:
=IF(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0))="","",[COLOR=#ff0000]if(TEXT(MIN(IFERROR(TIMEVALUE(LEFT(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0)):INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A52,SCHEDULE!$A:$A,0)-1),5)),1)),"hh:mm")="00:00",INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0))[/COLOR],TEXT(MIN(IFERROR(TIMEVALUE(LEFT(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0)):INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A52,SCHEDULE!$A:$A,0)-1),5)),1)),"hh:mm")&" - "&TEXT(MAX(IFERROR(TIMEVALUE(MID(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0)):INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A52,SCHEDULE!$A:$A,0)-1),7,5)),0)),"hh:mm")))
 
Upvote 0
Sorry, I was missing a parentheses in the above reply. Please use this instead

Code:
=IF(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0))="","",[COLOR=#ff0000]if(TEXT(MIN(IFERROR(TIMEVALUE(LEFT(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0)):INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A52,SCHEDULE!$A:$A,0)-1),5)),1)),"hh:mm")="00:00",INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0))[/COLOR],TEXT(MIN(IFERROR(TIMEVALUE(LEFT(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0)):INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A52,SCHEDULE!$A:$A,0)-1),5)),1)),"hh:mm")&" - "&TEXT(MAX(IFERROR(TIMEVALUE(MID(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0)):INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A52,SCHEDULE!$A:$A,0)-1),7,5)),0)),"hh:mm")))
thanks man, it's really fantasic

with character limitation of macro
is it mean one more range need to assign to run the new formula?

Code:
Sub Macro003()'
' Macro003 Macro
'


'
    Sheets("result").Select
    Dim rDate As Range, rDest As Range
    Dim sText1 As String, sText2 As String


    Set rDate = Range("c50")
    Set rDest = Range("c51")


    sText1 = "TEXT(MIN(IFERROR(TIMEVALUE(LEFT(INDEX('schedule'!$C:$C,MATCH('result'!$A51,'schedule'!$A:$A,0)):INDEX('schedule'!$C:$C,MATCH('result'!$A52,'schedule'!$A:$A,0)-1),5)),1)),""hh:mm"")"
    sText2 = "TEXT(MAX(IFERROR(TIMEVALUE(MID(INDEX('schedule'!$C:$C,MATCH('result'!$A51,'schedule'!$A:$A,0)):INDEX('schedule'!$C:$C,MATCH('result'!$A52,'schedule'!$A:$A,0)-1),7,5)),0)),""hh:mm"")"


    rDest.FormulaArray = "=IF(INDEX('schedule'!$C:$C,MATCH('result'!$A51,'schedule'!$A:$A,0))="""","""",1111&"" - ""&2222)"
    rDest.Replace "1111", sText1, LookAt:=xlPart
    rDest.Replace "2222", sText2, LookAt:=xlPart


    Range("c51").AutoFill Destination:=Range("c51:c" & Range("a" & Rows.Count).End(xlUp).Row)


    Range("b51").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR((VLOOKUP(RC1,'availability'!C1:C23,5,FALSE)),"""")"
    Range("b51").AutoFill Destination:=Range("b51:b" & Range("a" & Rows.Count).End(xlUp).Row)


End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top