Date VLookup

G

Guest

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
 
J

JulieD

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top