Hi,
I am trying to subtract two different dates & times to figure out the number of hours that have lapsed between the two, but cannot figure out a formula to work.
Here is a snippet of an example of what I need the difference between:
Example.PNG
Thank you so much.
surely its just the latest take away the other which is then in a serial number which you convert to days?
When I just subtract the two, it comes back as January 0, 1900
Convert the dates to numbers (may have to type in the long form)(June 12, 2019) which changes to 43628 days. etc... and just subtract the two cells to get the number of days.
Example:
July 1, 2019 changes to 01-Jul-19 in excel and June 30, 2019 changes to 30-Jun-19 and subtract the two is 1. If you're looking to add the time as well, you'll have to separate the time column... and convert it to a time value (6 hrs = .25 days)
Last edited by Cleetus09; 07-22-2019 at 03:03 PM.
Dates ARE numbers, so no need to convert them.Originally Posted by Cleetus09
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
What you need to understand about dates and times in excel is…
a date is just a number representing the number of days passed since 1/1/900...and then formatted in a way that we recognize as a date. So, for instance, today (Mon 22 Jul 2019) is actually 43668
Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75
So, January 0, 1900 is actually excel's way of saying the answer is 0 (zero), but is showing it in date format.
It looks to me like it should just be =C2-E2 ...formatted as General or Number.
Your answer will still have a decimal - from the TIME portion, you can remove that by using ROUND
=ROUNDDOWN(C2-E2,0)
"It looks to me like it should just be =C2-E2 ...formatted as General or Number.
Your answer will still have a decimal - from the TIME portion, you can remove that by using ROUND
=ROUNDDOWN(C2-E2,0)"
You just reiterated what i said earlier. You use convert to numbers. and subtract the two cells. and bingo bango there's your answer
No, I did not re-iterate what you said, there is no need to "convert to numbers" - as I have already said, dates are already numbers, they are just formatted by excel to show something we recognize as a date.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks