Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with date formula please.
I am trying to compare two lots of data. Once piece of data is coming from a
database which is holding the date field in format dd mm yyyy hh:mm. The other piece of data is in standard ddmmyy format. I need to compare the two dates to make sure they are the same. The problem i am having is that because one piece of date holds time data also it is not showing as being the same date even though it actually is. Can anyone please give me a formula which strips out the time element and will then compare. I have tried to use the day() month() year () function but with no luck. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with date formula please.
I suppose the date from the database is in text format:
=LEFT(SUBSTITUTE(B1," ",""),4)&MID(B1,9,2) converts it to ddmmyy text -- Kind regards, Niek Otten Microsoft MVP - Excel "julief" wrote in message ... I am trying to compare two lots of data. Once piece of data is coming from a database which is holding the date field in format dd mm yyyy hh:mm. The other piece of data is in standard ddmmyy format. I need to compare the two dates to make sure they are the same. The problem i am having is that because one piece of date holds time data also it is not showing as being the same date even though it actually is. Can anyone please give me a formula which strips out the time element and will then compare. I have tried to use the day() month() year () function but with no luck. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with date formula please.
Excel stores dates as numbers with Jan 1, 1900 equaling 1 and each date
afterwards is incremented by 1. Hours are equal to 1/24 and each minute equals 1/(24 * 60). A datte with no hours and minutes is equal to a whole number like 37524 and one with 8 hours (8/24) equals 37524.333333333. To compare the two numbers you simply need to use the INT function to round the number to a whole number 37524 = Int(37524.333333333) "julief" wrote: I am trying to compare two lots of data. Once piece of data is coming from a database which is holding the date field in format dd mm yyyy hh:mm. The other piece of data is in standard ddmmyy format. I need to compare the two dates to make sure they are the same. The problem i am having is that because one piece of date holds time data also it is not showing as being the same date even though it actually is. Can anyone please give me a formula which strips out the time element and will then compare. I have tried to use the day() month() year () function but with no luck. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with date formula please.
Thanks worked a dream
"Joel" wrote: Excel stores dates as numbers with Jan 1, 1900 equaling 1 and each date afterwards is incremented by 1. Hours are equal to 1/24 and each minute equals 1/(24 * 60). A datte with no hours and minutes is equal to a whole number like 37524 and one with 8 hours (8/24) equals 37524.333333333. To compare the two numbers you simply need to use the INT function to round the number to a whole number 37524 = Int(37524.333333333) "julief" wrote: I am trying to compare two lots of data. Once piece of data is coming from a database which is holding the date field in format dd mm yyyy hh:mm. The other piece of data is in standard ddmmyy format. I need to compare the two dates to make sure they are the same. The problem i am having is that because one piece of date holds time data also it is not showing as being the same date even though it actually is. Can anyone please give me a formula which strips out the time element and will then compare. I have tried to use the day() month() year () function but with no luck. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with date formula please.
Assuming that neither of the fields is text, and that they are Excel numbers
formatted as date and time, =INT(A2)=B2 is a suitable test. =INT(A2) gives you date without time. If you are starting with text, you ought to convert to Excel date & time, then work from there. -- David Biddulph "julief" wrote in message ... I am trying to compare two lots of data. Once piece of data is coming from a database which is holding the date field in format dd mm yyyy hh:mm. The other piece of data is in standard ddmmyy format. I need to compare the two dates to make sure they are the same. The problem i am having is that because one piece of date holds time data also it is not showing as being the same date even though it actually is. Can anyone please give me a formula which strips out the time element and will then compare. I have tried to use the day() month() year () function but with no luck. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with date formula please.
Thanks worked a dread
"David Biddulph" wrote: Assuming that neither of the fields is text, and that they are Excel numbers formatted as date and time, =INT(A2)=B2 is a suitable test. =INT(A2) gives you date without time. If you are starting with text, you ought to convert to Excel date & time, then work from there. -- David Biddulph "julief" wrote in message ... I am trying to compare two lots of data. Once piece of data is coming from a database which is holding the date field in format dd mm yyyy hh:mm. The other piece of data is in standard ddmmyy format. I need to compare the two dates to make sure they are the same. The problem i am having is that because one piece of date holds time data also it is not showing as being the same date even though it actually is. Can anyone please give me a formula which strips out the time element and will then compare. I have tried to use the day() month() year () function but with no luck. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with date formula please.
David, hopefully she meant "dream".
Fred. "julief" wrote in message ... Thanks worked a dread "David Biddulph" wrote: Assuming that neither of the fields is text, and that they are Excel numbers formatted as date and time, =INT(A2)=B2 is a suitable test. =INT(A2) gives you date without time. If you are starting with text, you ought to convert to Excel date & time, then work from there. -- David Biddulph "julief" wrote in message ... I am trying to compare two lots of data. Once piece of data is coming from a database which is holding the date field in format dd mm yyyy hh:mm. The other piece of data is in standard ddmmyy format. I need to compare the two dates to make sure they are the same. The problem i am having is that because one piece of date holds time data also it is not showing as being the same date even though it actually is. Can anyone please give me a formula which strips out the time element and will then compare. I have tried to use the day() month() year () function but with no luck. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Formula Problem - Leave date blank if Null | Excel Worksheet Functions | |||
Create a formula in a date range to locate a specific date - ecel | Excel Discussion (Misc queries) | |||
Excel Formula to calulate number of days passed from date to date | Excel Discussion (Misc queries) | |||
Formula for determining if two date columns fall within specific date range | Excel Worksheet Functions | |||
Formula for determining if two date columns fall within specific date range | Excel Discussion (Misc queries) |