How to Lookup Dates In Excel

[val2024]

Need help! I am trying to have the cells pre-populated based on date. I have changed the lookup date format to general and I am still getting an error.

 

[Joe4]

Welcome to the Board!

It is important to understand how Excel stores dates. It actually stores them as numbers, specifically the number of days since 1/0/1900.
That is why if you change a valid date to a General or Number format, you will see a number like: 45292
That is how Excel sees and stores the dates. So all dates really are in Excel are numbers with special date formatting.
Time is the fractional component of one day (i.e. 6:00 AM is 0.25).

Note that you may get missing results if you try to compare a valid date to a date entered as text, or if you are comparing a date with a time component to a date that does not have a time component. And changing the formatting to hide the time component does not get rid of it. It only hides it from view, but does not change the underlying value used in calculations.

In order to help you further, you would need to show us the formulas you are trying to use.

 

[val2024]

Welcome to the Board!

It is important to understand how Excel stores dates. It actually stores them as numbers, specifically the number of days since 1/0/1900.
That is why if you change a valid date to a General or Number format, you will see a number like: 45292
That is how Excel sees and stores the dates. So all dates really are in Excel are numbers with special date formatting.
Time is the fractional component of one day (i.e. 6:00 AM is 0.25).

Note that you may get missing results if you try to compare a valid date to a date entered as text, or if you are comparing a date with a time component to a date that does not have a time component. And changing the formatting to hide the time component does not get rid of it. It only hides it from view, but does not change the underlying value used in calculations.

In order to help you further, you would need to show us the formulas you are trying to use.

thank you for your help!

 

[Joe4]

When posting formulas, please just copy and paste them here. Posting images of formulas is not that helpful, as we cannot see it unless we click on the image, which then hides everything else. Also, we cannot copy/paste the formula from an image, if we wanted to copy it over to our side. You can use the XLS code tags around your formulas, if you want to make it look nice, but it is not necessary.

The problem with the image in your first post is that you are not showing us any row or column headers. So we cannot see what cells you formulas are referencing.

Note that MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

If you cannot use that tool, at least use an image of your data that show the row and column headers so we can align your formula to that.

 

[val2024]

When posting formulas, please just copy and paste them here. Posting images of formulas is not that helpful, as we cannot see it unless we click on the image, which then hides everything else. Also, we cannot copy/paste the formula from an image, if we wanted to copy it over to our side. You can use the XLS code tags around your formulas, if you want to make it look nice, but it is not necessary.

The problem with the image in your first post is that you are not showing us any row or column headers. So we cannot see what cells you formulas are referencing.

Note that MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

If you cannot use that tool, at least use an image of your data that show the row and column headers so we can align your formula to that.

thank you so much!!

 

[Joe4]

Your formula does not look right to me.
As matter as fact, I cannot even decipher it to tell what you are attempting to do.
Can you explain exactly what you are trying to do with this formula?
Use your data as an example and explain your desired result and why.

 

[val2024]

Your formula does not look right to me.
As matter as fact, I cannot even decipher it to tell what you are attempting to do.
Can you explain exactly what you are trying to do with this formula?
Use your data as an example and explain your desired result and why.

I am trying to pre-populate a monthly payment amount based on in service date and what the amortization period and total cost ((total cost/amortization period x 12)) is for that item.. For example, If the "Inservice Date" in H5 shows Jan 1, 2024, then I want the payment amount ((total cost/amortization period x 12))) to begin in the corresponding cell, L5.

 

[Alex Blakenburg]

Does this get you close:

20240630 Amortisation val2024.xlsx
	F	G	H	I	J	K	L	M	N	O	P	Q
4	Amount	Period	In Service Date	Lookup	End Date	Lookup	1/01/2024	1/02/2024	1/03/2024	1/04/2024	1/05/2024	1/06/2024
5	60000	5	1/01/2024		1/01/2029		1000	1000	1000	1000	1000	1000
Sheet1

Cell Formulas
Range	Formula
F5	F5	=1000*5*12
L5:Q5	L5	=IF(AND(L$4>=$H5,EOMONTH(L$4,-1)+1<=$J5),$F5/$G5/12,0)

 

[val2024]

Yay, it works, thanks so much!!!!!

 

[Alex Blakenburg]

You're welcome. Glad we could help.