| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| total hours vs total worked hours | Excel Discussion (Misc queries) | |||
| How to seperate OT and Reg. Hours from total hours | Excel Worksheet Functions | |||
| Adding minutes showing total in hours/minutes, i.e., 60 mins + 60 mins + 15 mins to total of 2 hours 15 mins? | Excel Discussion (Misc queries) | |||
| scheduled hours total | Excel Discussion (Misc queries) | |||
| Total hours with NETWORKDAYS | Excel Worksheet Functions | |||
Linear Mode
