Question about subtracting Dates/Time

[Monuda85]

This is one of the intermediate tutorials from Excel 2013.
Hi all, I am trying to subtract from C7 to B8 to get how long it took in decimal numbers. However, I keep getting an error -- #VALUE!
I might have typed/formatted the dates or time in an incorrect way or something. I would be appreciate your help to figure it out.​

 

[etaf]

check that B7 and C7 are dates and not text
you need to have the cell formatted as a date
I suspect you have text in the cell and not a date

Change the format to general and you should see a number
40987.27083
40988.58333

then format the cell d
as
[H]:MM
to show the result in hours

31.30

 

[Monuda85]

Right, that is a basic way to avoid the errors. However, I still haven't been able to see those numbers thus still struggling to get the desired result Could you elaborate step by step? (even though you already did...)

 

[MarkReddell]

Try formatting b7 & c7 by press Ctrl & 1, then date, then down to: 3/14/12 1:30 PM enter. I tried this & it works along with formatting d7, Ctrl & 1, click Custom, then in the Type zone type: [H]:MM This worx!!!

 

[Monuda85]

None of those methods work on my Excel Thank you for trying to help and finding out my problems. At least I know how to deal with them now!

 

[etaf]

can you attach a file using a share like dropbox or onedrive - I suspect the date format is the issue here


you would proably need to re-enter the date just as a date and time in 24hour format and then reformat to show the AM/PM

 

[Monuda85]

https://dl.dropboxusercontent.com/u/5192639/Add or subtract time.xlsx

Wow, I really appreciate your will to help me to the hilt. P.S. I just filled D7 by writing down the number instead of a proper calculation.

 

[etaf]

you need to change the way you enter the data and then format

see row 8 here
and also my notes

https://www.dropbox.com/s/lsz8qez7gk9mxjs/Add or subtract time_etaf.xlsx?dl=0

 

[Dave Patton]

try
=(C2-B2)*24

and format as number

 

[Monuda85]

Thank you so much~! I finally figured it out and got the result I wanted but I have a few questions as I have done not exactly in the same way as the tutorial.
1. How come the guy in tutorial manages to put the mm/dd/yyyy when I can only do dd/mm/yyyy?
2. For some reason, cell C7 is showing up as ######## but it looks fine when I double-click to reveal the format or print it.