|
total hours
Good day,
I would like to get help on the following. A B C D 19:00 16:00 21jul 21jul 19:00 08:00 21jul 22jul If it occurs that the date on C and D are the same and the time on B are earlier than time on A it should be 0. If time on date on C and D are the same an time on B are later than time on A it should give the difference. My main concern are if the date on D are later than C to get the difference between B and A as it should be 13hours. |
total hours
Try this.
I'm assuming that the date in D will never be less than the date in C. =IF(AND(C1=D1,B1<A1),0,((B1+D1)-(A1+C1))+(B1+D1<A1+C1)*(D1-C1)) Format as [h]:mm -- Biff Microsoft Excel MVP "ekkeindoha" wrote in message ... Good day, I would like to get help on the following. A B C D 19:00 16:00 21jul 21jul 19:00 08:00 21jul 22jul If it occurs that the date on C and D are the same and the time on B are earlier than time on A it should be 0. If time on date on C and D are the same an time on B are later than time on A it should give the difference. My main concern are if the date on D are later than C to get the difference between B and A as it should be 13hours. |
total hours
Isn't it as simple as =MAX(0,(B1+D1)-(A1+C1)) ?
Of course (for my formula and for Biff's) C and D will need to be dates, not text. -- David Biddulph "T. Valko" wrote in message ... Try this. I'm assuming that the date in D will never be less than the date in C. =IF(AND(C1=D1,B1<A1),0,((B1+D1)-(A1+C1))+(B1+D1<A1+C1)*(D1-C1)) Format as [h]:mm -- Biff Microsoft Excel MVP "ekkeindoha" wrote in message ... Good day, I would like to get help on the following. A B C D 19:00 16:00 21jul 21jul 19:00 08:00 21jul 22jul If it occurs that the date on C and D are the same and the time on B are earlier than time on A it should be 0. If time on date on C and D are the same an time on B are later than time on A it should give the difference. My main concern are if the date on D are later than C to get the difference between B and A as it should be 13hours. |
total hours
Isn't it as simple as =MAX(0,(B1+D1)-(A1+C1)) ?
I don't know. If the time span is = 24hrs the above formula fails. -- Biff Microsoft Excel MVP "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... Isn't it as simple as =MAX(0,(B1+D1)-(A1+C1)) ? Of course (for my formula and for Biff's) C and D will need to be dates, not text. -- David Biddulph "T. Valko" wrote in message ... Try this. I'm assuming that the date in D will never be less than the date in C. =IF(AND(C1=D1,B1<A1),0,((B1+D1)-(A1+C1))+(B1+D1<A1+C1)*(D1-C1)) Format as [h]:mm -- Biff Microsoft Excel MVP "ekkeindoha" wrote in message ... Good day, I would like to get help on the following. A B C D 19:00 16:00 21jul 21jul 19:00 08:00 21jul 22jul If it occurs that the date on C and D are the same and the time on B are earlier than time on A it should be 0. If time on date on C and D are the same an time on B are later than time on A it should give the difference. My main concern are if the date on D are later than C to get the difference between B and A as it should be 13hours. |
total hours
Ooops! Disregard.
It does work. I let Excel "help" and choose the format *it* wanted to use, h:mm instead of [h]:mm. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Isn't it as simple as =MAX(0,(B1+D1)-(A1+C1)) ? I don't know. If the time span is = 24hrs the above formula fails. -- Biff Microsoft Excel MVP "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... Isn't it as simple as =MAX(0,(B1+D1)-(A1+C1)) ? Of course (for my formula and for Biff's) C and D will need to be dates, not text. -- David Biddulph "T. Valko" wrote in message ... Try this. I'm assuming that the date in D will never be less than the date in C. =IF(AND(C1=D1,B1<A1),0,((B1+D1)-(A1+C1))+(B1+D1<A1+C1)*(D1-C1)) Format as [h]:mm -- Biff Microsoft Excel MVP "ekkeindoha" wrote in message ... Good day, I would like to get help on the following. A B C D 19:00 16:00 21jul 21jul 19:00 08:00 21jul 22jul If it occurs that the date on C and D are the same and the time on B are earlier than time on A it should be 0. If time on date on C and D are the same an time on B are later than time on A it should give the difference. My main concern are if the date on D are later than C to get the difference between B and A as it should be 13hours. |
| All times are GMT +1. The time now is 07:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com