IF DATE STATEMENT

ChetanPuri

Board Regular
Joined
Sep 5, 2018
Messages
58
Office Version
  1. 365
Platform
  1. Windows
Hi All Excel Wizards,

I am trying to create an if Statement in column S based on anniversary date in column H. I am trying to have a statement that if Anniversary date is =<01/01/2024, then divide the next level salary by 365 Days and then multiply by no. of days (1/01/2024-Anniversary date), however if Anniversary date is => 01/01/2024 then pick the values from next column, but I get False if anniversary date is more than 01 Jan 2024. Any help will be appreciated.
Employee positions report_31 March 2023.xlsx
S
239,386.14
Raw Data to flow in worksheets
Cell Formulas
RangeFormula
S2S2=IF(H2<=Summary!$B$2,'Raw Data to flow in worksheets'!P2/365*(Summary!$B$2-'Raw Data to flow in worksheets'!H2))
 

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.
Book3
BCDEFGHIJKLMNOPQRST
1Anniv DateSalaryNext Column
21/1/2412/23/2335000863.013699123
31/1/243500087788778
41/2/2435000123123
Sheet1
Cell Formulas
RangeFormula
S2S2=IF(H2<$B$2,P2/365*DATEDIF(H2,$B$2,"d"),IF(H2>=$B$2,P2))
S3:S4S3=IF(H3<$B$2,P3/365*DATEDIF(H3,$B$2,"d"),IF(H3>=$B$2,T3))


something like this? if false pick which column?
 
Last edited:
Upvote 0
Book3
BCDEFGHIJKLMNOPQRST
1Anniv DateSalaryNext Column
21/1/2412/23/2335000863.013699123
31/1/243500087788778
41/2/2435000123123
Sheet1
Cell Formulas
RangeFormula
S2S2=IF(H2<$B$2,P2/365*DATEDIF(H2,$B$2,"d"),IF(H2>=$B$2,P2))
S3:S4S3=IF(H3<$B$2,P3/365*DATEDIF(H3,$B$2,"d"),IF(H3>=$B$2,T3))


something like this? if false pick which column?
Perfect thank you much appreciated
 
Upvote 0
Hi Rudd,
Sorry to bother you is there a formula which I tried to create in Column S Row 34. I am trying to do is if Date in Colum H is less than 01 January 2024 then subtract 01/07/2023 and divide the amount in column M/365 and multiply it by number of days i.e 01/01/2024-01/07/2023, otherwise, if Greater than 01 Jan 2024 then subtract it from 30 June 2024- date in Column H and then divide amount in column R * times by no. of days. Thank you for all your help

Salaries Budget 23-24 - 30 April 2023.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZ
11/07/20231/01/202430/06/2024
2Employee CodeSurnameGivenPosition NumberPosition TitleStart Date (with org)Contract Expiry DateAnniversary DateEmployee TypeHours/Fortnight (as at EOM, inc flexible arrangements etc)ClassificationStepCurrent SalaryDivision DescriptionNext StepNext Level Salary - FullNext Salary Level - Incudes P-T CalculationAs Per New EBAJuly to 31 December 2023Anniversry date to NEW EBAEBA to End of Financial YearTotal SalarySUPERA/L LoadingWork CoverSalary with Oncost
3303173Health Workforce Consultant4-Jan-224-Jan-24Secondment76.00L6199,357.00Health Workforce2105,290.00105,290.00107,395.8050,086.82882.7152,373.84103,343.3611,367.77417.352,066.87#########
431621Executive Coordinator3-May-223-May-24Permanent76.00L4177,112.00Health Workforce283,045.0083,045.0084,705.9038,872.9028,544.7313,460.1280,877.748,896.55326.621,617.5591,718.47
597173Health Workforce Consultant31-Aug-1731-Aug-23Permanent60.80L63109,305.00Health Workforce3109,305.0066,457.4467,786.59#VALUE!36,834.2933,614.72#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!
6350178Health Workforce Support Officer8-Sep-228-Sep-23Permanent76.00L4177,112.00Health Workforce283,045.0083,045.0084,705.9014,366.0726,164.8642,004.8482,535.789,078.94333.321,650.7293,598.75
7207280Health Workforce Development Manager23-Apr-1923-Apr-24Permanent76.00L81125,793.00Health Workforce2130,360.00130,360.00132,967.20102,012.9541,165.1924,771.97167,950.1118,474.51678.263,359.00#########
833244Health Workforce Programs Manager8-Aug-228-Aug-23Permanent76.00L83134,927.00Health Workforce3134,927.00134,927.00137,625.5413,677.5353,970.8068,247.19135,895.5214,948.51548.812,717.91#########
917948Health Workforce Consultant8-Oct-188-Oct-23Permanent76.00L62105,290.00Health Workforce3109,305.00109,305.00111,491.1028,269.6425,454.5955,287.37109,011.6011,991.28440.242,180.23#########
10339320Health Workforce Support Officer29-Aug-2229-Aug-23Permanent76.00L4177,112.00Health Workforce283,045.0083,045.0084,705.9012,253.4128,440.0742,004.8482,698.339,096.82333.971,653.9793,783.08
1134661Health Workforce Support Officer5-Sep-225-Sep-23Permanent76.00L4177,112.00Health Workforce283,045.0083,045.0084,705.9013,732.2726,847.4242,004.8482,584.549,084.30333.511,651.6993,654.05
12296264Health Workforce Policy and Strategy Lead##################Permanent76.00L72117,152.00Health Workforce3121,830.00121,830.00124,266.6044,614.0514,686.3661,622.62120,923.0213,301.53488.342,418.46#########
13217296Health Workforce Coordinator - Locum29-Jul-1929-Jul-23Permanent76.00L5187,493.00Health Workforce293,426.0093,426.0095,294.526,472.0839,930.0247,255.6493,657.7410,302.35378.231,873.15#########
14314247Health Workforce Senior Coordinator - Outreach7-Mar-227-Mar-24Permanent76.00L6199,357.00Health Workforce2105,290.00105,290.00107,395.8067,780.5319,419.5133,837.03121,037.0813,314.08488.802,420.74#########
1533060Health Workforce Consultant20-Jul-2220-Jul-23Permanent76.00L6199,357.00Health Workforce2105,290.00105,290.00107,395.804,899.8047,596.8553,256.55105,753.1911,632.85427.082,115.06#########
1641248Health Workforce Coordinator - Education18-Sep-0618-Sep-23Permanent60.80L5396,036.00Health Workforce396,036.0058,389.8959,557.6920,522.7627,626.7929,534.0977,683.648,545.20392.151,553.6788,174.67
17277162Health Workforce Education Team Lead##################Permanent76.00L71111,221.00Health Workforce2117,152.00117,152.00119,495.0443,878.9712,517.6159,256.44115,653.0312,721.83467.062,313.06#########
18331319Health Workforce Coordinator - Policy and Strategy8-Aug-228-Aug-23Permanent76.00L5187,493.00Health Workforce293,426.0093,426.0095,294.528,869.1537,370.4047,255.6493,495.1910,284.47377.581,869.90#########
1922059Health Workforce Access Team Lead19-Aug-1919-Aug-23Permanent76.00L73121,830.00Health Workforce3121,830.00121,830.00124,266.6016,021.4845,060.4161,622.62122,704.5113,497.50495.542,454.09#########
2022447Health Workforce Engagement Team Lead23-Sep-1923-Sep-23Permanent76.00L72117,152.00Health Workforce3121,830.00121,830.00124,266.6026,640.0433,378.0861,622.62121,640.7413,380.48491.242,432.81#########
21364323Workforce Planning Consultant##################Permanent76.00L6199,357.00Health Workforce2105,290.00105,290.00107,395.8038,653.9611,827.1053,256.55103,737.6011,411.14418.942,074.75#########
2223050Future Health Workforce Consultant8-Oct-198-Oct-23Permanent76.00L62105,290.00Health Workforce3109,305.00109,305.00111,491.1028,269.6425,454.5955,287.37109,011.6011,991.28440.242,180.23#########
236652Health Workforce Coordinator19-Jul-9919-Jul-23Permanent55.60L5396,036.00Health Workforce396,036.0053,396.0254,463.944,472.9143,676.6527,008.1475,157.708,267.35414.891,503.1585,343.09
Sheet2
 
Upvote 0
Hi Sorry for late reply, I'm still trying to understand the conditions that you create.

CASE1 Date in Column H < 01/01/2024
Formula 1: (Column H date - subtract 01/07/2023) / (amount column M / 365) * (ex 01/01/2024 - 01/07/2023)
Formula 2: (amount column M / 365) / (Column H date - subtract 01/07/2023) * (ex 01/01/2024 - 01/07/2023)
Case2 Column H >= 01/01/2024
Formula 1: substract 30 june 2024 - date in column H / amount column R * (01/01/2024- column H date)
Formula 2: amount column R / substract 30 june 2024 - date in column H * (01/01/2024- column H date)

Formula 1 or formula 2 these two which one is correct? or can you give the expected result that you're looking for?

Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZ
2Employee CodeSurnameGivenPosition NumberPosition TitleStart Date (with org)Contract Expiry DateAnniversary DateEmployee TypeHours/Fortnight (as at EOM, inc flexible arrangements etc)ClassificationStepCurrent SalaryDivision DescriptionNext StepNext Level Salary - FullNext Salary Level - Incudes P-T CalculationAs Per New EBAJuly to 31 December 2023Anniversry date to NEW EBAEBA to End of Financial YearTotal SalarySUPERA/L LoadingWork CoverSalary with Oncost
3303173Health Workforce Consultant445651/4/24Secondment76L6199357Health Workforce2105290105290107395.850086.81644882.705205552373.84219103343.363811367.77002417.34820012066.867277117195.3493
431621Executive Coordinator446845/3/24Permanent76L4177112Health Workforce2830458304584705.938872.8986328544.7279513460.1156280877.742198896.551641326.62165121617.55484491718.47033
597173Health Workforce Consultant429788/31/23Permanent60.8L63109305Health Workforce310930566457.4467786.5888#VALUE!36834.2876733614.71938#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!
6350178Health Workforce Support Officer448129/8/23Permanent76L4177112Health Workforce2830458304584705.914366.0712326164.8630142004.8435682535.777819078.935559333.31756421650.71555693598.74649
7207280Health Workforce Development Manager435784/23/24Permanent76L81125793Health Workforce2130360130360132967.2102012.953441165.1879524771.97151167950.112918474.51242678.26007123359.002258190461.8876
833244Health Workforce Programs Manager447818/8/23Permanent76L83134927Health Workforce3134927134927137625.5413677.5315153970.868247.18559135895.517114948.50688548.8088192717.910342154110.7431
917948Health Workforce Consultant4338110/8/23Permanent76L62105290Health Workforce3109305109305111491.128269.6438425454.5890455287.3674109011.600311991.27603440.2391552180.232005123623.3475
10339320Health Workforce Support Officer448028/29/23Permanent76L4177112Health Workforce2830458304584705.912253.413728440.0684942004.8435682698.325759096.815833333.97400791653.96651593783.08211
1134661Health Workforce Support Officer448099/5/23Permanent76L4177112Health Workforce2830458304584705.913732.2739726847.4246642004.8435682584.542199084.299641333.51449731651.69084493654.04717
12296264Health Workforce Policy and Strategy Lead4451811/18/23Permanent76L72117152Health Workforce3121830121830124266.644614.0493214686.3561661622.61534120923.020813301.53229488.34296872418.460416137131.3565
13217296Health Workforce Coordinator - Locum436757/29/23Permanent76L5187493Health Workforce2934269342695294.526472.08493239930.0164447255.6386893657.7400510302.35141378.2331811873.154801106211.4794
14314247Health Workforce Senior Coordinator - Outreach446273/7/24Permanent76L6199357Health Workforce2105290105290107395.867780.5287719419.5145233837.03288121037.076213314.07838488.80357682420.741523137260.6996
1533060Health Workforce Consultant447627/20/23Permanent76L6199357Health Workforce2105290105290107395.84899.7972647596.8493253256.5474105753.19411632.85134427.08020642115.063879119928.1894
1641248Health Workforce Coordinator - Education389789/18/23Permanent60.8L5396036Health Workforce39603658389.88859557.6857620522.7616427626.7945229534.0852777683.641438545.200557392.15299761553.67282988174.66782
17277162Health Workforce Education Team Lead4415811/23/23Permanent76L71111221Health Workforce2117152117152119495.0443878.9698612517.6109659256.44449115653.025312721.83278467.06029452313.060506131154.9789
18331319Health Workforce Coordinator - Policy and Strategy447818/8/23Permanent76L5187493Health Workforce2934269342695294.528869.15342537370.447255.6386893495.1921110284.47113377.57673741869.903842106027.1438
1922059Health Workforce Access Team Lead436968/19/23Permanent76L73121830Health Workforce3121830121830124266.616021.4794545060.4109661622.61534122704.505813497.49563495.53742712454.090115139151.6289
2022447Health Workforce Engagement Team Lead437319/23/23Permanent76L72117152Health Workforce3121830121830124266.626640.0438433378.0821961622.61534121640.741413380.48155491.24145552432.814827137945.2792
21364323Workforce Planning Consultant4488611/21/23Permanent76L6199357Health Workforce2105290105290107395.838653.9561611827.0958953256.5474103737.599511411.13594418.94030552074.751989117642.4277
2223050Future Health Workforce Consultant4374610/8/23Permanent76L62105290Health Workforce3109305109305111491.128269.6438425454.5890455287.3674109011.600311991.27603440.2391552180.232005123623.3475
236652Health Workforce Coordinator363607/19/23Permanent55.6L5396036Health Workforce39603653396.01654463.936324472.90958943676.6465827008.1437675157.699938267.346992414.88547691503.15399985343.0864
24
25
26
27
28
29
30
31
32
33Form1Form2
340.004972261810.04157CASE 1Date in Column H < 01/01/2024
350.08422082179634.926Formula 1: (Column H date - subtract 01/07/2023) / (amount column M / 365) * (ex 01/01/2024 - 01/07/2023)
3625.0546178603.840781Formula 2: (amount column M / 365) / (Column H date - subtract 01/07/2023) * (ex 01/01/2024 - 01/07/2023)
3737.5593293352.109589
38
39Case 2Column H >= 01/01/2024
40Formula 1: substract 30 june 2024 - date in column H / amount column R * (01/01/2024- column H date)
41Formula: amount column R / substract 30 june 2024 - date in column H * (01/01/2024- column H date)
Sheet2
Cell Formulas
RangeFormula
R34:R37R34=LET( Case,IFS(H3<DATE(2024,1,1),"C1",H3>=DATE(2024,1,1),"C2"), subsdate,IFS(Case="C1",DATEDIF(DATE(2023,7,1),H3,"d"),Case="C2",DATEDIF(H3,DATE(2024,6,30),"D")), Amt,IFS(Case="C1",M3/365,Case="C2",R3), Muldiffdate,IFS(Case="C1",DATEDIF(H3,DATE(2024,1,1),"D"),Case="C2",DATEDIF(DATE(2024,1,1),H3,"d")), subsdate/Amt*Muldiffdate)
S34:S37S34=LET( Case,IFS(H3<DATE(2024,1,1),"C1",H3>=DATE(2024,1,1),"C2"), subsdate,IFS(Case="C1",DATEDIF(DATE(2023,7,1),H3,"d"),Case="C2",DATEDIF(H3,DATE(2024,6,30),"D")), Amt,IFS(Case="C1",M3/365,Case="C2",R3), Muldiffdate,IFS(Case="C1",DATEDIF(H3,DATE(2024,1,1),"D"),Case="C2",DATEDIF(DATE(2024,1,1),H3,"d")), Amt/subsdate*Muldiffdate)
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

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