Elapsed time Problem #name error.

**Sklaar** · 04-03-2014, 02:24 PM

Hi,

Rather new to using excel and I have been trying to get excel to calculate elapsed time between two dates using mostly what I've read from other elapsed time posts. But I am clearly missing a step. I'm trying to streamline my process for taxes. Appreciate any help.

I want to take a time in the format of " yy/mm/dd hhmm " example: 13/04/24 2245 ( note its a 24hr clock)
13/04/24 2245 would represent 2013 April the 24th at 10:45PM.

------- A --------------- B -------------------- C
13/04/24 2245 ------13/05/24 0900 ---------[display hrs elapsed] <-- currently get #name?

I have custom formatted A and B to yy/mm/dd hhmm and formatted c to [h]:mm
When you enter b-a in column c you get #name?

If its easier to have the date and time into separate columns I can give that a shot, I was just trying to make it as streamlined as possible for copy past.
Either way it will be quicker and easier than performing hundreds manually.

**Tony Valko** · 04-03-2014, 02:28 PM

What cells are those dates/times in?

Post the exact formula you tried that returns the #NAME? error.

**Sklaar** · 04-03-2014, 02:41 PM

Column A has the start date and time ( A1)
Column B end date and time (b1)
Column C want the result (c1)

It was my understanding that due to the way excel interprets dates into a numerical value that the calculation for C would simply be =B-a.
However maybe part of the problem is that both the date and the time are in the same cell ?

**Tony Valko** · 04-03-2014, 03:03 PM

If the entries are valid dates+times then this formula should work:

=B1-A1

**Sklaar** · 04-03-2014, 03:15 PM

Originally Posted by Tony Valko

If the entries are valid dates+times then this formula should work:

=B1-A1

A friend sent me this and it works

=TEXT((20&LEFT(LEFT(B1,8),2)&"/"&MID(B1,4,2)&"/"&MID(B1,7,2)&" "&LEFT(RIGHT(B1,4),2)&":"&RIGHT(B1,2))-(20&LEFT(LEFT(A1,8),2)&"/"&MID(A1,4,2)&"/"&MID(A1,7,2)&" "&LEFT(RIGHT(A1,4),2)&":"&RIGHT(A1,2)),"[h]:mm")

However that only works re using the same row 1.

The problem is When I enter the date into a cell as 13/24/05 0945 it won't work resulting in a error, but if I add the ":" into the time and use 2013 instead it works fine. Because excel doesn't understand what I am entering as a date.

So The question is how do I make it work without needing to add those.

**Tony Valko** · 04-03-2014, 03:41 PM

Let's take a step back and look at this from a practical point of view.

If you enter the dates/times as Excel expects them to be entered, for example:

A1 = 1/25/2014 10:45 PM
B1 = 1/27/2014 11:30 PM

Then we can use this SIMPLE formula:

=B1-A1

On the other hand, if we enter the dates/times as TEXT strings like:

A1 = 13/04/24 2245
B1 = 13/05/24 0900

Then we can use this COMPLICATED formula:

=TEXT((20&LEFT(LEFT(B1,8),2)&"/"&MID(B1,4,2)&"/"&MID(B1,7,2)&" "&LEFT(RIGHT(B1,4),2)&":"&RIGHT(B1,2))-(20&LEFT(LEFT(A1,8),2)&"/"&MID(A1,4,2)&"/"&MID(A1,7,2)&" "&LEFT(RIGHT(A1,4),2)&":"&RIGHT(A1,2)),"[h]:mm")

Hmmm...

From a practical point of view which method looks easier to maintain and work with?

Why fight with Excel when you can work with it and make your life a lot easier.

**Sklaar** · 04-03-2014, 04:04 PM

I understand your point, but I have hundreds of these time periods I need to calculate. I'm trying to save time from having to type the date and time into each cell manually. Thus allowing
me to copy and paste it directly from my works online sort of pay system which presents the date in time that specific format.

**Sklaar** · 04-03-2014, 04:25 PM

A1 = 13/04/24 2245
B1 = 13/05/24 0900
You get 730:15 hrs

But I made a typo shouldn't be a full month time period merely the next day of same month day which is 10:15 hrs.
A1 = 13/04/24 2245
B1 = 13/04/25 0900

Anyways, I've tinkered enough for now, maybe I'll mess around some more another time. I have to actually do the work now. Thanks !

**Tony Valko** · 04-03-2014, 04:31 PM

Originally Posted by Sklaar

A1 = 13/04/24 2245
B1 = 13/05/24 0900
You get 730:15 hrs

I think the correct result is 706:15.

A1 = 13/04/24 2245
B1 = 13/04/25 0900

Try this...

Data Range

	A	B	C
1	13/04/24 2245	13/04/25 0900	10:15
2	------	------	------

This formula entered in C1:

=(--(20&LEFT(B1,8))+TEXT(RIGHT(B1,4),"00\:00"))-(--(20&LEFT(A1,8))+TEXT(RIGHT(A1,4),"00\:00"))

Format as [h]:mm

Elapsed time Problem #name error.

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Elapsed time Problem #name error.

Re: Elapsed time Problem #name error.

Re: Elapsed time Problem #name error.

Re: Elapsed time Problem #name error.

Re: Elapsed time Problem #name error.

Re: Elapsed time Problem #name error.

Re: Elapsed time Problem #name error.

Re: Elapsed time Problem #name error.

Re: Elapsed time Problem #name error.

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Creating a Time elapsed Spread Sheet that records and posts time...

Adding elapsed time to date/time format over 24h

[SOLVED] Obtaining elapsed time in decimal when time and date are in different columns - v2003

Using start/end time fields on userform to populate elapsed time on spreadsheet

Trying to understand a formula to calculate elapsed time in a specific time period

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions