IF with OR and Today Functionality

S

Shinianne

New Member
Joined
Aug 6, 2018
Messages
24
Office Version
  1. 365
Hi,

Is it possible to associate =today() to if

this is an example: the current tab i'd like for it to change from EMEA to AMER if today the work time is 10 PM onwards and be change to EMEA if the shift was changed to 5PM, is that possible can i kindly ask for help please thank you

AbsentAB
Vacation LeaveVL
Half DayHD
Adjusted
6-May7-May8-May9-May10-May11-May12-May13-May14-May15-May16-May17-May18-May19-May20-May21-May22-May
NAMELEADStatusCURRENTNEW INOUTMondayTuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesdayWednesday
BillableEMEAEMEA5:00 AM2:00 PM5AM5AM5AMRDRD5AM5AM5AM5AM5AMRD3PM3PMRD3PM3PM3PM
BillableAPACEMEA5:00 AM2:00 PM5AM5AM5AM5AM5AMRDRD5AM5AM5AM5AMRDRD3PM3PM3PMRD
BufferAPACEMEA5:00 AM2:00 PM5AM5AM5AM5AM5AMRDRD6AM6AM6AM6AM6AMRDRD4PM4PM4PM
BillableAPACEMEA5:00 AM2:00 PM5AM5AMRD5AMRD5AM5AM5AM5AMRD5AM5AMRD5AM3PM3PM3PM
BillableAPACEMEA6:00 AM3:00 PM6AM6AM6AM6AM6AMRDRD5AM5AM5AM5AM5AMRDRD4PM4PM4PM
BillableAPACEMEA7:00 AM4:00 PMRDRD7AM5AM5AM5AM5AMRD2PM2PM2PM2PM2PMRD2PM2PM2PM
BillableAPACEMEA7:00 AM4:00 PMRDRD7AM7AM7AM7AM7AMRD3PM3PM3PM3PM3PMRD3PM3PM3PM
BillableAPACEMEA7:00 AM4:00 PM7AM7AM7AM7AM7AMRDRD7AM7AM7AM7AM7AMRDRD4PM4PM4PM
BillableAPACEMEA7:00 AM4:00 PM7AM7AMRDRD7AM7AM7AM7AMRD7AM7AM7AM7AMRD3PM3PM3PM
BillableEMEAAMER2:00 PM11:00 PM2PM2PM2PM2PM2PMRDRD2PM2PM2PM2PM2PMRDRD8PM8PM8PM
BillableEMEAAMER2:00 PM11:00 PM2PM2PM2PM2PM2PMRDRD2PM2PM2PMRDRD11PM11PM11PM11PM11PM
BillableEMEAAMER2:00 PM11:00 PM2PM2PM2PM2PM2PMRDRD2PM2PM2PM2PM2PMRDRD8PM8PM8PM
BillableEMEAEMEA2:00 PM11:00 PM2PM3PM3PM3PM3PMRDRD2PM2PM2PM2PM2PMRDRD2PM2PM2PM
BillableAMERAMER2:00 PM11:00 PM2PM2PM2PM2PM2PMRDRD8PM8PM8PM8PM8PMRDRD8PM8PM8PM
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
Hello,

I think I figured out what you need. I would suggest adding a cell that says Today's Date somewhere on your sheet and using the Today() function to automatically update the date each time you open the workbook. In my example below, Today is in A1 and =TODAY() is in B1. Current is in A3. I set the formula to see if the date in cell B1 matches the date in row 3 and the return value is 10PM then it will give the New column AMER. If not then it will use the current value as the new value.

Today6/10/2019
CurrentNew6/9/20196/10/2019
EMEAAMER9PM10PM
EMEAEMEA7PM8PM

<colgroup><col span="2"><col><col></colgroup><tbody>
</tbody>

CurrentNew6/9/20196/10/2019
EMEA=IF(INDEX($C:$D,MATCH($B$1,$3:$3,0)*0,2)="10PM","AMER",A4)9PM10PM
EMEA=IF(INDEX($C:$D,MATCH($B$1,$3:$3,0)*0,2)="10PM","AMER",A5)7PM8PM

<tbody>
</tbody>
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
vlookup, INDEX, or SUMPRODUCT???
2
Replies
10
Views
331
KRice
K
T
  • Question
Reg Hr, OT and DT
Replies
3
Views
519
awoohaw
awoohaw
C
  • Solved
Convert and copy to Date
Replies
5
Views
106
Micron
M
C
  • Question
Calculate and Identify Ordinary Hours, Double time or 1.5 Time
Replies
0
Views
71
ChetanPuri
C
B
  • Solved
Firing Pete Best
Replies
2
Views
320
BloodyBill
B

Forum statistics

Threads
1,214,832
Messages
6,121,847
Members
449,051
Latest member
excelquestion515

Share this page

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