Date VLookup
How do I calculate the time difference in HH:MM to show the difference
between the two start date & time and end date & time. I know how to do if if I have the date and time in the same cell, but I need them in different cells for a second step which requires an IF THEN statement that won't work if they are combined. The issue I keep having is the second example returns a number too large to display in the cell. I think this is because if I subtrack 1:00 AM from 4:00 PM it is a negative number. Can someone help. Also, is there a formula to determine what day of the week it is based on the date? A B C D 11/1/04 6:00 PM 11/1/04 6:05 PM 11/2/04 4:00 PM 11/3/04 1:00 AM 11/2/04 8:00 PM 11/3/04 6:10 PM 11/5/04 5:00 AM 11/5/04 5:03 AM 11/6/04 9:00 PM 11/6/04 9:15 PM 11/6/04 2:00 PM 11/6/04 2:25 PM -- Court |
Hi
in column E how about =(C1+D1)-(A1+B1) with what day of the week - do you want to see Mon / Tue or 1 (being the 1st day of the week), if Mon you can either customise the format of the cells to display this (ddd will give you Mon, dddd will give you Monday) or in another cell type =TEXT(A1,"ddd") or =TEXT(A1,"dddd") for the other type there is a WEEKDAY function which will return the day of the week e.g. =WEEKDAY(A1) check out this in help for more details Cheers JulieD "Court" wrote in message ... How do I calculate the time difference in HH:MM to show the difference between the two start date & time and end date & time. I know how to do if if I have the date and time in the same cell, but I need them in different cells for a second step which requires an IF THEN statement that won't work if they are combined. The issue I keep having is the second example returns a number too large to display in the cell. I think this is because if I subtrack 1:00 AM from 4:00 PM it is a negative number. Can someone help. Also, is there a formula to determine what day of the week it is based on the date? A B C D 11/1/04 6:00 PM 11/1/04 6:05 PM 11/2/04 4:00 PM 11/3/04 1:00 AM 11/2/04 8:00 PM 11/3/04 6:10 PM 11/5/04 5:00 AM 11/5/04 5:03 AM 11/6/04 9:00 PM 11/6/04 9:15 PM 11/6/04 2:00 PM 11/6/04 2:25 PM -- Court |
All times are GMT +1. The time now is 10:08 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com